Jump to content
Larry Ullman's Book Forums

Help Explain This Database Design Please.


Recommended Posts

How does This table allow for multiple authors for one book?

if it doesn't pleae tell me how to implement it.

(page 176, chapter 6 - normalization)

 

PUBLISHERS

publisher id

name

address1

address2

city

state

zip

 

BOOKS

book_id

publisher ID

title

 

BOOKS AUTHORS

book_id

Author_id

 

 

AUTHORS

author_id

author

 

 

I'm not sure how everything ties together,...please explain it more. why doesn't BOOKS reference to author? like if you were searching it...shouldn't the author be a foreign key so it's in the books table??

also, why are all the ids with an underscore except publisher id? I'm not sure if that matters.

Link to comment
Share on other sites

The BOOKS_AUTHORS table is the table that maintains the many-to-many relationships and foreign keys.

 

For example, imagine the following books and IDs:

 

BOOKS

1 Rocket Science for Dummies

2 DBs Are Fun

 

AUTHORS

1 James Brown

2 Billy Kid

3 Arthur Smith

 

Now, let's assume that James Brown and Billy Kid co-authored Rocket Science for Dummies and Arthur Smith was the only author of DBs Are Fun. If that's the case, then your BOOKS_AUTHORS table would look like the following:

 

BOOKS_AUTHORS

1 1

1 2

2 3

 

In other words, the Rocket Science for Dummies book (with ID 1) would be in the BOOKS_AUTHORS table twice, once for each author (with IDs 1 and 2).

 

In order to get all the necessary info for various queries, you would have to use joins.

Make sense?

  • Upvote 1
Link to comment
Share on other sites

I think "easy" is such a relative term (no pun intended).

 

When most people (including myself) first start learning DB design, their first instinct is to put everything in one table (and there are some NoSQL experts who might still argue that point).

However, as we all know, in order to avoid redundancy and all sorts of DB inconsistencies, it's essential that we separate our data into separate tables.

To that end, all many-to-many relations should be broken up.

 

If you keep that one rule in mind and use a little common sense, I think you'll more or less be okay.

Of course, a lot of experience pays off too, so I would recommend checking back here occasionally if you have any questions. There are people far more knowledgeable than me on these forums that can help.

 

Good luck with your project.

Link to comment
Share on other sites

 Share

×
×
  • Create New...