Hello :)
I'm wondering what approach you should use when building a schema for an auction website. The part that is confusing is when sellers are allowed to 'extend the auction for last-minute bidders' so that towards the last 10 minutes of the auction, the END_DATE extends for 20 seconds each time a new user places a bid. There would be a countdown for 20 seconds, and each time a new bid comes in the counter would reset to 20 seconds. This could go on for up to 3 days (72hrs).
Here is what I have:
1) The listings table, where all details about the auction goes 2) The vendors table, where all user details go 3) A relational table which are dependent on both of the above, to log any changes on the auction.
drop table if exists LISTINGS;
CREATE TABLE LISTINGS ( /*Rows in this table are dependent on a table called 'Vendors'*/
L_REF INT(11) UNSIGNED ZEROFILL NOT NULL PRIMARY KEY AUTO_INCREMENT/*up to 4 billion+ listings allowed*/
, V_REF INT(11) NOT NULL /*the seller's ref*/
, L_IMG INT(1) DEFAULT 1 NOT NULL /*If listing has an image, use 0*/
/*BY DEFAULT IMG STATUS IS SET TO 1 (NO IMG UPLOADED/FOUND). WHEN 0 OR 2 IMG OR IMG URL IS FOUND.*/
, L_TITLE VARCHAR(256) NOT NULL
, L_PRICE DECIMAL(20, 4) NOT NULL /*The starting price of the auction*/
, L_DESCRIPT TEXT NOT NULL
, L_QTY INT(11) NOT NULL /*Minimum quantity is 1*/
, L_TAGS INT(1) DEFAULT 1 NOT NULL /*If listing has tags, use 0*/
, L_OPEN_DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL /*If vendor selects a different time to start the auction, it will display here*/
, L_END_DATE TIMESTAMP /*Value would either be 1, 3, 5 or 7 days from the Open Date*/
, L_CREATION_DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL /*Date auction was created*/
, L_LAST_MODIFIED TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL /*Last time auction was modified, updates on edit*/
/*WHEN ITEM IS ORDERED, QTY ORDERED GOES TO THIS COLUMN. IF NO PAYMENT, QTY IS PUT BACK. AFTER PAYMENT, QTY IS REMOVED AS SOLD.*/
, L_QTY_RESERVED INT(11) NOT NULL
, FOREIGN KEY (V_REF) REFERENCES VENDORS (V_REF)
) AUTO_INCREMENT=7989777;
The vendors table:
drop table if exists VENDORS;
create table VENDORS ( /*all users are 'vendors' whether they are buyers or sellers*/
V_REF INT(11) UNSIGNED ZEROFILL NOT NULL PRIMARY KEY AUTO_INCREMENT /*up to 4 billion users allowed*/
, V_EMAIL VARCHAR(126) NOT NULL
, V_CELL VARCHAR(20) NOT NULL
, V_USERNAME VARCHAR(256) NOT NULL
, V_PASS VARCHAR(256) NOT NULL
, V_FNAME VARCHAR(126) NOT NULL
, V_LNAME VARCHAR(126) NOT NULL
, V_LEVEL TINYINT(2) DEFAULT -1 NOT NULL
, V_DATE_CREATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
) AUTO_INCREMENT=7939786;
This is the 'logging' table which will be updated each time a user adds a new price for the auction:
drop table if exists LIVE_AUCTIONS;
create table LIVE_AUCTIONS (
/*Rows in this table are dependent on two tables, 'Listings' and 'Vendors'*/
/*A new row will be added to this table each time the winning price of the auction is updated*/
L_A_ID INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT /*this bid's identifier*/
, L_REF INT(11) NOT NULL /*the product reference*/
, V_REF INT(11) NOT NULL /*The bidder's reference*/
, L_A_WINNING_PRICE DECIMAL(20, 4) NOT NULL /*Price that the highest bid is at*/
, L_A_MAXIMUM_PRICE DECIMAL(20, 4) NOT NULL /*this bidder's maximum price*/
, L_A_EXTENDED_DATE TIMESTAMP /*The new end date. If no extended dates were made, use the original end date found in listings table*/
, L_A_TIMESTAMP TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL /*the timestamp this bid was made. Non changeable.*/
, FOREIGN KEY (L_REF) REFERENCES LISTINGS (L_REF)
, FOREIGN KEY (V_REF) REFERENCES VENDORS (V_REF) /* (all users - buyers and sellers alike- are termed as vendors here)*/
);
Is this a sound table structure? It might not be the best way to do this, but I haven't come across a better idea. I've seen a lot of people ask about auction website database designs but none of them presented their table structure, so I'm giving it a try.
Any suggestions, tips, or tricks from those out there who done/would like to do a project like this? :)
Along with these SQL queries, if you can add an ERD diagram or something, that will be much helpful. Easy to understand.
Also, security is not always depended on the structure of DB. There are a lot of things to do
I some minor things, I will not go into the structure but more about the nomenclature of it.
why upper case ?
if you're on an case insensitive environment like windows it does not matter anyhow and if not it's just annoying writing CAPS ALL THE TIME. Plus you loose the easy visual distinction between what is SQL and what are the fields etc
SELECT * FROM my_table WHERE ....stay implicit in the direct context (this can be arguable but we can create aliases in queries)
CREATE TABLE `my_table` ( id int unsigned auto_increment, .... CREATE TABLE `my_reference_table` ( my_table_id int unsigned .... FOREIGN KEY (my_table_id) REFERENCES `my_table` (`id`) ....and in queries
SELECT * FROM `my_table` mt INNER JOIN `my_reference_table` mrt ON mt.id = mrt.my_table_id ...This is about philosophy I know a lot of database guys do the explicit every id is unique thing. I don't see it as a necessity also namespacing all fields all the time does not always make sense although you avoid involuntary column collisions ... it makes it harder to screw up but it lets you write way more code.
It's a question of taste.
another thing would be for created and modified just use 'created_at' and 'modified_at' so it's clear what they mean but it's less to write.
I personally hate fname just write first_name you already being overly expressive and it's way better to read.
Some normal forms are missing but as soon as you hit 1+ million you usually have to break some normalizations.
also check if the datetimes use a BTREE index (usually they do) and if you do explicit hits on names in search indexes try to use a HASH index since you don't need range search patterns.
I don't know about your login system if you use the username everything is fine but if you want to switch to emails at some later point the email should be unique.
those are my initial thoughts, they are all highly debatable I guess.