Which do you perfer?

[HP400]

Say you got 2 tables
'shelf' and 'book'

shelf has a shelf_id

book has a book_id

Both unique to there respected tables.

To related these would you...
1) Add shelf_id into book and thus have a composite key.
or
2) Create a seperate table that has shelf_id and book_id to make the relationship.

Thoughts?

[bogu]

U dont have to create another table to link thoes 2 tables, what is happening when u need to link another table to book, create another table?

[HP400]

I agree, just couldn't think of a reason. Thanks.

[sickofit]

create a relation table.. i think thats the fastest way..
2 values, both indexes.

ONLY if u need to link them in BOTH ways (shelfs looking for books and books looking for shelfs)

[Piranha]

It depends pretty much on what you define as a book. Are part 1 and part 2 of the same book one or two books? If there are 20 parts, is it one or 20 books?

And if they are one book, can the different parts be placed on more than one shelf?

If the answer to the last question is yes then you need a table between and use a composite key. That will enable you to place one book on more than one shelf.

If the answer is no the best way to solve it is to add shelf_id as a foreign key in book. Note that it's no good idea to use a composite key, if you do then you will not have the possibility to move a book without removing the book from the database and add it again.