Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
What's the relationship between an Order and a Sale? If an Order is just the sale of a single book, then you could just add seller_user_id, sale_value_total and sale_value_tax. Or do you mean Sale as in "a list of items for sale but not yet sold"? –  Rick Mar 18 at 14:03
    
The latter is what I meant. Unless i would also have to make a Purchase/Order_Details table that then has a one to many relationship with the Order table. –  Rodney Mar 18 at 14:26

2 Answers 2

up vote 0 down vote accepted

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.)
share|improve this answer
    
Thank you for the clarification Rick, it makes a lot more sense on how to approach the design. So sales_history would/could be the order_details? –  Rodney Mar 18 at 22:49
    
Yes, you could use the order_details as sales_history. –  Rick Mar 19 at 16:46
  1. You don't sell to users, you sell to parties (companies or individuals). Some might play the role of users.

  2. There are many parties involved in a sales/purchase order:

    • the vendor (you if you sell)
    • the buyer (you if you buy)
    • a party selling something on consignment
    • sales staff taking commission
    • the party that placed the order
    • the party that took the order (a clerk)
    • the party that is paying for the order
    • the parties to whom to ship the order items

Do yourself a favour and get a data model pattern book and don't reinvent the wheel :)

Ready-to-Use Database models example

share|improve this answer
    
I would think that if this is an online system (registration by email suggests that), all buyers must be users. Even Amazon used to create anon user accounts if buying without logging in. As for the other types of users, that's what refactoring is for (eg: add a user_type column.) No point designing for what you might never use, you're bound to miss a future use case and end up having to refactor regardless. –  Rick Mar 18 at 17:35
    
I agree with Rick. –  Rodney Mar 18 at 22:51
    
It's a waste of time to re-invent the wheel when quality data model patterns exist for this exact problem. –  Neil McGuigan Mar 19 at 17:33

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.