MySQL – Database Design Logic for a Secondhand Bookstore Project

database-designdatabase-recommendation

I am currently working on a small eCommerce based project that allows users to buy/sell second hand books to/from other users. I am having a hard time making design decisions on how to model the database. My requirements are as follows;

Users (should be able to both buy and sell books)
– User can sell a book.
– User can buy a book.

Temp Users
– This is for registering a user through email

Orders
– many orders can be made by one user.

Order_Details (I am yet to create it)
– stores information on each product that has been orderd

Products/Books
– many products belong to one category

Here is my first try at the design;

Database Design Draft 1

  1. I'm having an issue on how to incorporate buying and selling for one user.I don't want to create a second users table for selling (unless that's the only way to do it.) products only.

  2. Should I create a "sales" table where it holds the users_ID and information that holds the book that is meant to be sold?Would this be a temporary table like the temp_users table?

Any opinions would be helpful.

Regards

Best Answer

Yes, you could use one table to track both sold and unsold items. It would be like a transaction table in an OLTP, with the distinction that the transaction remains open until the sale is closed. In theory that's a good idea, as that would be 3NF (assuming listings and sales are 1-1), but it practice it's more common to denormalize the for sale listings from the actual sale transactions. There are several practical reasons for this, I can elaborate if you need to justify the decision.

If decide to combine them into one table, it would be a sale_transactions table with a many-to-many relationship to users. The buyer_user_id would be null until the item was sold, and it should also include things like product_id, listing_price, listing_date, sale_date and taxes.

If you're willing to separate the tables, you would have a for_sale table and a sales_history (or just sales or similar) table. You might want to combine the sales_history with the orders table, it depends on the way you plan to populate them.

  • In the for_sale table is the listing_id, listing_user_id, product_id, list_price, listing_date and units_available. This represents the items for sale, and has the advantage of having only one row with multiple units for sale (whereas a combined sale_transaction table as above would only work for individual units.) As units are sold, the available_units goes down until it reaches zero. At that point you can either leave the row for historical records, or remove it. (Though multiple units might not apply to this case, if sellers usually only have one copy of a book.)
  • In the sales_history table is the listing_id, the buyer_user_id, the sale_date, and probably the order_id (depends how you want to associate orders to sales.)

So the buy/sell scenarios would be:

  1. User lists a book for sale. New entry in the for_sale table with the product, seller, etc.
  2. User searches for books for sale. Query the for_sale table joined to the products and categories tables to get a listing of all products for sale and their details.
  3. User buys a book. New entry in the sales_history table with the listing, the buyer, etc. Entry in the for_sale table is updated to decrement the units_available by 1. (Or else delete the row, mark it sold with a flag, or some other way to indicate it's not available.)
Related Question