Best way to do an author-book database?

[dcnguyen]

I'm familiar with terms like many-to-many but am no expert...What I'd like to do is create an authors table and a books table, with a key connecting the two. The problem, as I see it, is that a book can have many authors, and an author can have many books. What's the best way to structure the tables? Should the book's table have multiple entries for the same book, if it has multiple authors?

 

[RiverGuy]

The way one constructs many to many relationships is usually done with a third table. The third table will have one-to-many relationships with the book table and the author table. For example, it may consist of three fields (BookAuthorID PK IDENTITY, BookID FK, AuthorID FK). This way, you store each book title only once, and each author is stored only once. Occurences of someone authoring a book are recorded in the third table.

 

[johnwm]

Read and understand the excellent "Fundamentals of Relational Database Design" by Paul Litwin. It's available as a free download from :

http://www.alphabetcitydataworks.com/fundamentals.htm

It is a 'must read' for all database designers

___________________________________________________________
If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller
Steam Engine Prints

 

[araragona]

Thanks for the tip. I had my Administrator take a look. There was a Networking issue. Before, I only had to type in the Network computer node and the name of the shared folder to Network in and have full access. For some reason, and they only accidentally stumbled onto the solution, I had to type in the whole path to the shared folder. It was strange because I can still Network into the old computer using the old method, but not the new one. Who knows? Maybe it was a security measure on the new computer to make sure someone could not easily stumble into a shared folder.

 

[lespaul]

post above in reference to Thread669-1351557

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[jrbarnett]

You don't say anything about the purpose of this system, but if this is for a library, there may well be multiple copies of the same book. If this is the case, then the best practise I have seen is to give the "Book" table a name of title, then use book as an instance of a title - which would have the same key plus a numer (for example a library book number).
If it is for a book store, and there can be multiple instances of a book, then you want to put a stock quantity in the book table to differentiate between items.

John

 

[johnherman]

This is the computer field. Almost everything has been done before and its hard to build a better mousetrap. Therefore, refer to the Library of Free Data Models

http://www.databaseanswers.org/data_models/index.htm

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright