Database design- Joining tables

[anikaam]

Hello,

I have a database to manage a library. I have created a table for "books" with a primary key (p.k.) "coddoc", another one for "authors" with p.k. "codaut" and a third one
with only two colums which are coddoc and codaut, which is related with the two previous ones.
For books with more than one author I get more than one row when executing select.

I wonder if another table design would be better. I could for example have new columns in my books table which would refer to author.

Thank you

Ana

 

[r937]

no, keep what you have

if a book has multiple authors, then store the book once, store the author once, and the "relationship" table will store the keys relating books to authors

presentation, though, is another matter

for example, if you join the relationship keys to their parent rows, you get something like this:

Practical Web Database Design -- Chris Auld
Practical Web Database Design -- Allan Kent
Practical Web Database Design -- Rudy Limeback
Practical Web Database Design -- Nigel Stanger
Practical Web Database Design -- Thearon Willis

most people wish to show this as

Practical Web Database Design
Chris Auld, Allan Kent, Rudy Limeback, Nigel Stanger, Thearon Willis

this is an issue best left to the reporting program

don't compromise the data design


rudy

 

[shadedecho]

agreed, you are setting up a many-to-many relationship, meaning, an author can be listed under more than one book, as well as one book may have more than one author. The most sensible way to set up a flexible (and relatively infinitely scaleable) solution to this is to have two tables with one instance of their respective item, and then multiple instances of the "relationship" in a third table, just as you have done.

the reporting program, as r937 points out, will be the one responsible for outputting this data in a reasonable and readable fashion. Luckily, this can be done (in this scenario) with SQL Joins pretty easily.