Database Design for a Book Store

[redverse22]

Hi
I am designing a database for a bookstore.

Customer can place and order
Customers can leave a review for a books in catalogue but can only leave 1 review per book
Books will be defined in one genre only
For this example a book will be written by one author ( I know really you would need a new table as technically it be a M:M relationship)

Books can de defined by author, genre and publisher.

All cardinality be 1:M
I have the following tables

Customer - details
Order details - customer id and book id posted as FK + details for order
Author - author details
Publisher - publisher details
Genre - details
Book Table - with Author/Publiser and Genre posted as FK + details
Review table - Book id and customer id posted as FK. + details

I think the tables are all in 3NF but I just feel from looking at it, my UML diagram is wrong. After tying myself in confusion and knots trying to figure out if it is all right.

Advice if I am correct or totally wrong be appreciated. Thanks

 

[spikepl]

Order systems normally allow ordering more than one item per order. Your curretn structure seemingly doesn't.

If you have a diagram to look at, then critique via ESP is not as reliable as when the critics actually get to see it.

 

[RainLover]

As Spike says.

You really need to post a copy of your Database.

Attach in Version 2003 as most people do not have 2007 or 2010.

 

[redverse22]

As Spike says.

You really need to post a copy of your Database.

Attach in Version 2003 as most people do not have 2007 or 2010.

I attached a copy now. I have been a drawing package to do out UML's as that is what I am used to using. I have it as JPEG though so hope that is ok.

It is not complete as I said got confused but this is what I have so far-it does need rework I bet.

 

[spikepl]

Looks OK with the exception of OrderDetails (and no "Orders") . If your customer can only order one book in one order then thats fine. If one can order more than one book in one shot, then more work is needed.

Update: What is "address" doing in OrderDetails?

BTW: Which app did you use to draw?

 

[RainLover]

From what I can see it looks like you are heading in the right direction.

Once you construct your Tables in Access post the Database and we can have a proper look.

But for now no suggestions except to keep going.

 

[spikepl]

IMHO, an ERD is quite enough to prevent major calamities

 

[redverse22]

Looks OK with the exception of OrderDetails (and no "Orders") . If your customer can only order one book in one order then thats fine. If one can order more than one book in one shot, then more work is needed.

Update: What is "address" doing in OrderDetails?

BTW: Which app did you use to draw?

I am using Gliffy as a applicaion. It very good for Database design, wireframes etc. I friend of mine uses it for work and recommended it to me. So def give it al go you can get a free trial for 30 days I think and after that can keep a basic verison or sign up for a year.

Thanks for looking at the ER. Address is an error in order details - was supposed to delete it.

This is where I am getting slightly confused about the order tables.
Given a realistic enough situation you may be ordering more than 1 books I want to account for that so

Would I keep the orders table and have in it - Order Details id, customer id

Then if there is more than 1 book per order -not totally sure how to procced from there?

 

[RainLover]

You would have a Table for the Books.

e.g. tblOrderDetails.

You would store the Primary Key from the Table Orders as a foreign Key. You can do this several times for more than one book.

 

[spikepl]

An order would normally have customer info (ie the Customer ID), OrderDate etc. An order detail would be a specific item ordered (item id, quantity, unit price) with that order, each record related to the specific order by OrderID.

Edit: OOps, no "unit price" - that is a fixed item in the Book list I presume. But "sales price" , which may be influenced by rebates, offers, whatever.

 

[jdraw]

There is an existing free data model for a bookstore, that may be useful to you, at
http://www.databaseanswers.org/data_models/bookstore/bookstore_dezign.htm

For your info, there is a series of videos related to Database design, Normalization, ERD etc, all based on a Customer, Order, OrderItem approach.
You can get the particulars from

post #2 at http://www.access-programmers.co.uk/forums/showthread.php?p=1146957#post1146957

 

[redverse22]

An order would normally have customer info (ie the Customer ID), OrderDate etc. An order detail would be a specific item ordered (item id, quantity, unit price) with that order, each record related to the specific order by OrderID.

Edit: OOps, no "unit price" - that is a fixed item in the Book list I presume. But "sales price" , which may be influenced by rebates, offers, whatever.

Thanks for all the help to you and RainLover. It been very beneficial and you just do not think of certain things at times and how much detail is needed at times.

I have revised my ER and have it attached. I think I finally got it right ( cross fingers and toes anyway), inspiration thanks to ye. Best way to learn things with a bit of informative help.

If ye do not mind having a quick look

 

[Pat Hartman]

Author and genre do not belong in the book table. Both have a man-to-many relationship with a book. Books frequently have co-authors so you would have to decide how many authorIDs to store or do it right and create a junction table to store the Book/Author combinations. Same for genre. Many books cross lines and so should be listed under several genre's. The Nancy Drew mysteries for example. They are mysteries and also children's books.

FYI - avoid column names like NAME and DATE. They are reserved words in most languages and will cause trouble once you get to implementation. The best method of naming is to use adjectives and nouns. ie. CustomerName, AuthorName, PurchaseDate, etc. These will rarely conflict with anything and are more descriptive so you'll have better built-in documentation.

 

[redverse22]

Author and genre do not belong in the book table. Both have a man-to-many relationship with a book. Books frequently have co-authors so you would have to decide how many authorIDs to store or do it right and create a junction table to store the Book/Author combinations. Same for genre. Many books cross lines and so should be listed under several genre's. The Nancy Drew mysteries for example. They are mysteries and also children's books.

FYI - avoid column names like NAME and DATE. They are reserved words in most languages and will cause trouble once you get to implementation. The best method of naming is to use adjectives and nouns. ie. CustomerName, AuthorName, PurchaseDate, etc. These will rarely conflict with anything and are more descriptive so you'll have better built-in documentation.

Thanks for the advice.

However I mentioned above for the purpose of this database books with only 1 author will be entered and books will be attributed to one genre. ( not totallly realistic I know but at the moment it is what is needed)

I will probably expand upon this later and as you said since they a M:M relationships- 2 new tables will have to be created. This is what I would do -


Books_Author Table - (book_id, author_id)
Book_Genre Table (book_id, genre_id)

New Book table (book_id, title, publisher_id, description, ISBN, price)


ER diagram was just a draft I would be naming the attributes properly during database creation - just wanted to ensure the most important design was correct first.

 

[RainLover]

(Not totally realistic I know but at the moment it is what is needed)

Pat Hartman always gives sound advice and you would be the better programmer for following her advice.

You say at the moment. Things have a way of changing when you least expect.

To alter the design LATER you may have a lot of work.

Suggest that you DO IT NOW.

A programmer should program for Possibilities, not Probabilities.

 

[redverse22]

(Not totally realistic I know but at the moment it is what is needed)

Pat Hartman always gives sound advice and you would be the better programmer for following her advice.

You say at the moment. Things have a way of changing when you least expect.

To alter the design LATER you may have a lot of work.

Suggest that you DO IT NOW.

A programmer should program for Possibilities, not Probabilities.

No you are totally correct it is better to have all possibilities built in at the start.

 

[redverse22]

(Not totally realistic I know but at the moment it is what is needed)

Pat Hartman always gives sound advice and you would be the better programmer for following her advice.

You say at the moment. Things have a way of changing when you least expect.

To alter the design LATER you may have a lot of work.

Suggest that you DO IT NOW.

A programmer should program for Possibilities, not Probabilities.

You are totallly right it much better to build things in at start then levaing them out and having to go back. Consider every possibility.

I agree Pat gives great advice and I appreciated it. Hope my post did not come across as ungrateful.

So if I go back and add into my ER the following 2 tables

Books_Author Table - (book_id, author_id)
Book_Genre Table (book_id, genre_id)

Then adjuts the book table as follows

New Book table (book_id, title, publisher_id, description, ISBN, price)

It will all make logical and realisitc sense & I be good to go to start creating the database. Finally

 

[redverse22]

Author and genre do not belong in the book table. Both have a man-to-many relationship with a book. Books frequently have co-authors so you would have to decide how many authorIDs to store or do it right and create a junction table to store the Book/Author combinations. Same for genre. Many books cross lines and so should be listed under several genre's. The Nancy Drew mysteries for example. They are mysteries and also children's books.

FYI - avoid column names like NAME and DATE. They are reserved words in most languages and will cause trouble once you get to implementation. The best method of naming is to use adjectives and nouns. ie. CustomerName, AuthorName, PurchaseDate, etc. These will rarely conflict with anything and are more descriptive so you'll have better built-in documentation.

Thanks Pat. Advice greatly appreciated

So if I go back and add into my ER the following 2 tables

Books_Author Table - (book_id, author_id)
Book_Genre Table (book_id, genre_id)

Then adjust the book table as follows

New Book table (book_id, title, publisher_id, description, ISBN, price)

I should be ok then ?

 

[RainLover]

Once you finally build your tables, please post the DB so we can have a look.

Then you will receive some good feedback.

Currently your pics are limited.

I don't know why you use that program as Access does the same thing only better.