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?
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 16: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