Bookstore Database Design

Question

I am going to create a bookstore in which we have 3 objects (classes): Seller, Buyer, Book. I developed the database as follows:
- Both the buyer and the seller can buy / sell one or more books, respectively.
The buyer needs a seller account if he wants to sell the book.
- Buyers will bid, and the seller would like to sell to the best buyer, and I must save all the information among them.

In this model the process class will be the other three classes connector:
    seller book buyer 
   ------- ------ -------
     sID * bID * byID * 
     name -> sID     

This was my first thought & then I found out that this schema will fail in the process due to a buyer could buy multiple books at the same time and there were other reasons, too. so I changed it:

In this model the process class will be the other three classes connector:
     ______ ______ ________ _______
    | seller | | book | | process | | buyer |
    -------- -------- ---------- ---------
    | sID * | | bID * | | pID * | | byID * |
    | name | | XXX | -> | sID | | date & .. |
                              ----------
(*) indicates a primary key  

this will work better I think, but how to get into work with Price offers ?
yes, I can add a offer to the process class, so I've changed my mind & this model came into the place: (sorry for long description)

The * Offer * field will be added to the * process * class: ______ ______ _________ _______ | seller | | book | | process | | buyer | -------- -------- ----------- --------- | sID * | | bID * | | pID * | | byID * | | name | | XXX | -> | sID | | offer | | date & .. | ----------- (*) indicates a primary key

I am completely confused with db design because of this for the first time. Will this satisfy the needs of the system? If not, how can I make it work? If so, is there a better design?

Any suggestion is appreciated, thanks in advance :)

update - , . . , ^ o ^

Answer 1

( ), ?

(.. Moby Dick ... eBay, Amazon), . . :

Cust table
cust_id
name
is_buyer
is_seller

Book table
book_id
description
seller_cust_id
buyer_cust_id

. , , / . , . , /login/etc. ...

2. , , , , ?

Answer 2

, , :

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

, ( ) . , , . , . , - , , . Bid .

, , Bid / , .

, , . , , , . Expiry , , , , .

/, , , , .

Answer 3

, , - . . (, , ), . , SID. , .

Answer 4

LuckyLindy, , Customer, Order (, Process) OrderItem. - ... , . OrderItem ( Order ).

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

, , . , Googling.

Answer 5

, :

• , , .
• . , 3 "", 3 .
• , : seller ( ) buyer. "" .

• "" , AuctionProcess. Bid. .

  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 , , NULL, .

This is very similar to (just noticed) Ankur's answer, only with the addition of a relationship winning_bid_id.

Answer 6

This seems a bit confusing: a date model, an object model, functional and non-functional specifications and requirements - parts of each are discussed. Keep designation and design elements clean, clean, and separate.