Book Store Database Design

Answer 1

If a buyer can be a seller (and vice versa), why not have a single customer table with a set of flags for account type?

If books are unique (i.e. one copy of Moby Dick is viewed separate from another copy ... more like eBay than Amazon), then your book table could have a buyer and seller foreign key. Your store's simplest design is now down to two tables. For example:

Cust table
cust_id
name
is_buyer
is_seller

Book table
book_id
description
seller_cust_id
buyer_cust_id

Edit: I don't think this solution changes even if a single buyer/seller must have two accounts. You would just add the restriction in your app that a customer cannot be both a buyer and seller. The nice thing about one table is that you don't need to duplicate security/login/etc. logic ...

Edit 2: Also, if a buyer can buy multiple books, wouldn't it make more sense to have a shopping cart table that stores each book purchased by a buyer?

Answer 2

If I understand you correctly, the buyers will place offers before any seller does anything. Then a seller can accept one of the pending offers. I would add a buyer ID to the process (in fact, a process would be created when a buyer places an offer), as well as the book ID and the offered price. Once a seller then closes the deal, the process can be link to the seller with the sID field. This way a buyer could hold several simultaneous offers on different books.

Answer 3

Taking LuckyLindy's comment a step further, use a single Customer table, create a table called Order (probably akin to your Process table), and a table called OrderItem. Your Buyers and Sellers are all Customers ... if your Orders are strictly between two people then your Order table can contain buyer and seller fields. For each item in an Order, you have a tuple in OrderItem (with an ID back to Order to bind the group).

Customer    Order       OrderItem
--------    -----------   ---------
id          id (pk)       id
name        date          order_id (fk)
            buyer (fk)    book
            seller (fk)   price
            total

This is really a rudimentary example, but it's shaping up that your requirements could just use a common shopping cart design. There's lots of examples you can find by Googling.

Answer 4

I think you need a domain model similar to the following:

Buyer(BuyerId, ... buyer details ... )
Seller(SellerId, ... seller details ... )
Book(BookId, ... book details ... )
Bid(BidId, BuyerId, BookId, Price, Expiry)
Offer(OfferId, SellerId, BookId, Price, Expiry

How it works is that a user (Buyer or Seller) can create a Bid or Offer as appropriate. So if you are a buyer you may start by searching through the Offers available. If you like one you may choose to accept it and proceed to checkout. Perhaps you don't quite like any of the Offers but one is close in price to what you want. You might create a Bid and have that sent to the creator of the Offer to consider.

Or if nothing is close to your requirements as a buyer you may create a Bid and leave it in the system for potential Sellers to browse/search and consider till time of expiry you have chosen.

I have added the Bid and Offer classes and I think their benefit is self explanatory. But if you would like some further explanation please feel free to leave a comment and I will respond. The Expiry fields are not necessary but most likely every Bid and Offer will have a time limit in which case you will need them.

I have increased the number of classes/tables, but I think you will find that your system becomes much easier to manage and extend.

Answer 5

Well, accoring to the specification I would make a few assumptions:

• Buyers and Sellers need seperate accounts - so they have to be atomic and must not share data, hence we will have two tables.
• A book is a real-world instance. So, if a seller wants to sell 3 books of "The Hobbit", that would result in 3 table-rows.
• Hence the book might get to attributes: the seller (read-only) and the buyer. But this doesn't work with the "process" of waiting for incoming offers from buyers.

• Since there might be "batches" of book-buyings it makes sense to create a AuctionProcess entity. Also I would add another Bid entity. This results in the following scheme.

  Seller(id*)
  Buyer(id*)
  Book(id*, seller_id)
  AuctionProcess(id*, book_id, winning_bid_id, end_date, start_date?)
  Bid(id*, process_id, buyer_id, price)
  

  winning_bid_id will of course be NULL until a winner is chosen by the buyer.

It is quite similar to (just noticed) Ankur's response, only with the addition of the winning_bid_id relation.

Answer 6

It does seem a bit confusing, the datamodel, the object model, functional and non-functional specs and requirements - parts of each being being discussed. Keep the notation and design elements clean, clear and separate.