Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2006

    ER modelling for the database of online bookstore (bookshop)


    how would the ER diagram for the database of an online bookshop look like? I only know a few records that can be included in the Database so far such as Book details eg. Title, Author, ISBN, and then Customer details such as name, email etc., AND ORDER details etc. but the DB may be bigger than this. so any1 have a ER diagram for this kind?


  2. #2
    Join Date
    Jun 2004
    Arizona, USA
    Well, what kinds of data would you need?

    Customer data

    Sales Order Data

    Supplier Data

    Purchase Order data

    Shipment Data

    Break down each class of data into more discrete information. For instance, the customer data would include, at a minimum, a name (first, last, middle initial) address (Street, PO Box, city, state/Province, Postal Code, Country) Telephone number(s), etc. Probably, the simplest way of joining the customer to other tables would be to add a surrogate primary key (customer number) as well.

    The sales order data would include a sales order number, timestamp, customer reference, requested delivery date, shippment method, etc. Possibly the tax rate, as well.

    Note that I did NOT include the details of the order here. Each item of an order (you CAN order more than one book from the book store...) would be stored in a separate child table. Call it SalesOrder_Detail, maybe. It would include a foreign key to the parent sales order table, an item or line number within the order (1-N), and the combination (AKA a Compound Key) of these two fields would form the unique key (PK) for this table. In addition, it would need to describe the detail of the order line item - the quantity, the as-sold price each, and the part number (ISBN Number, maybe) and possibly a discount percentage rate. In a normalized database you would NOT include the line item total as a separate field, as it is a derived, or calculated field.

    You could find some more basic information on table design and normalization in the database installments of this tutorial.

    I'll leave it up to you to refine the other classes of data you'll need to store.
    Last edited by loquin; 03-10-06 at 15:50.
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert

  3. #3
    Join Date
    Apr 2002
    Toronto, Canada
    loquin, a very generous response to a RFH* question

    i trust buddy appreciates it

    RFH = request for homework | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Mar 2006
    thanks loquin

  5. #5
    Join Date
    Mar 2006
    i see Loquin thanks I think I have managed to create my tables thanks to ur tutorials, BUT my problem now is how to normalise data see my thread

    i have attached the normalisation table there.

    oh yeah R937 this is not a RTF. I am trying to learn database for educational purposes.

  6. #6
    Join Date
    Jul 2003

    A helpful Data Model

    Check out this Data Model which will give you a Kick-Start :-

    Senior DBA

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts