DB design question

[Solomons]

What is the best design for a books-author database?

This is a many to many (each book has many authors and each author can have many books) realationship.

Thanks in advance

 

[dmcmunn]

This is a start, but you can find many better examples of such a common teaching question...what class you in ? ;o)

Book --1:M--< Author_Book >--M:1-- Author

Book
------
Book_ID
Book_Name
Book_In_Print
Book_ISBN
Book...

Cascade deletes to Author_Book_Bridge


Author
------
Author_ID
Author_Name
Author_City
Author...

Cascade deletes to Author_Book_Bridge

Author_Book_Bridge
------------------
Author_ID (FK to Author)
Book_ID (FK to Book)

 

[Solomons]

Thanks a lot that was a really well explained answer
But can this be improved any further?
Joins are required over 3 tables and in some cases the data in the "Bridge" tables may be too few to really justify 3 tables.
Can this be .. well denormalized?

 

[r937]

Solomons, do yourself a favour, become experienced with the three table design for a many-to-many relationship

you should be able to construct the sql to answer questions like

- which books have more than one author
- which authors have written only one book
- average number of authors per book
- etc.

once you have a better feel for this design, only then should you even consider different design options

and when you consider different design options, remember to give them a good workout by constructing queries like those i mentioned

because by then you will realize just how good this design really is



rudy

http://r937.com/

SQL Consulting

 

[shannanl]

Rudy, I have a question. I have only been using SQL for about a year so I am no expert, not even close so I am always trying to learn new and better ways to do something. I would have set up the Books_T and Authors_T like you did but why set up a 3rd table? Couldn't you just do joins on the Author_id and Book_id from two tables?

Thanks,

Shannan

 

[r937]

the third table tells you which author(s) wrote which book(s)

try setting up some test data for yourself

21 curly
22 larry
23 moe
24 shemp
25 joe
26 curly joe

4012 the art of the woob
5231 i was just standin there
6385 eeb eeb eeb eeb
7356 why i oughta..
8266 i'm a victim of soicumstance

4012 21
5231 22
6385 24
8266 21
7356 23
7356 24
7346 22

which books did curly write

who didn't write a book

which book has more than one author

rudy

http://r937.com/

SQL Consulting

 

[r937]

last question: which book-author row contains a typo that would have been prevented if foreign keys were being enforced

rudy

http://r937.com/

SQL Consulting