Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
monaya
Yak Posting Veteran
58 Posts |
 Posted - 2009-01-29 : 10:54:54
|
| Let's say you have books stored in a table, but every book will be labeled with labels that users can create in another table. Should I have 2 tables, a BOOKS table and a LABELS table with a bookid inside the labels table.or should it really be 3 tables, i.e. a new 3rd table that storesboth primary keys of the BOOKS and LABELS tables? |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-01-29 : 10:58:21
|
Thast depends on the relation between books and labels.3 tables needed when relation is many : manyWebfred
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-01-29 : 10:58:51
|
| What is cardinality between Books and Labels? If it is 1-many,you can go with 2 tables with fk key(bookid) pointing to pk(bookid) in Books table. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-01-29 : 10:59:02
|
It depends. Can same label be used on two different books?
E 12°55'05.63"N 56°04'39.26" |
|
|
|
monaya
Yak Posting Veteran
58 Posts |
Posted - 2009-01-29 : 11:45:34
|
quote:
Originally posted by Peso
It depends. Can same label be used on two different books?
Well every label can be assigned to many books and every book can have many labels. I guess sort of like the tag system?(Is that many to many?)Since you asked that question you made me think of another issue with a BOOKLIST table, which is sort of the opposite. Any book can be attached to a list created by the user, but ONLY ONE. Should I attach the BOOKLIST id to the BOOKS table or create another table with both primary keys. (This sounds like 1 to many?)In both cases, a BOOK does not have to be labeled and a BOOK does not have to be in a list which is why i thought they both need a third table to guide the relationship more efficiently.Thinking of it gets me confused even more. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-29 : 11:56:43
|
| b/w BOOK and LABEL you need a connecting third table as you told with bookid and labelid as columns which fk to corresponding table ids.for linking BOOK and BOOKLIST also create a third table which cntain booklistid and bookid. make bookid unique key if you want to enforce that no book can be in more than one list |
|
|
|
monaya
Yak Posting Veteran
58 Posts |
Posted - 2009-01-29 : 13:59:49
|
| so both get 3 tables not just the many to many relationship?What would the query look like if I wanted to display all books and attach data from the book list table. What if the book has no associated list wouldn't it throw an error |
|
|
|
|
|
|
Database design - 2 or 3 tables of related data?