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 |
|
bogey
Posting Yak Master
166 Posts |
 Posted - 2003-10-01 : 08:56:49
|
| I've got a request to add functionality to an existing program. I've been given a list of Categories and Keywords associated with the Categories. Categories are unique and Keywords can be assigned to multiple categories. What would be a good table design for this. I'm thinking a table for categories with unique CategoryId and a Keyword table with foreignKey of CategoryID and primaryKey of KeywordId along with Keyword Description. Category Table: Allergy/Immunology/Infectious Disease 1 Behavioral Health 2 Cardiology/Vascular System 3 Dentistry 4 Dermatology 5KeyWord Table: 1 12 Aging 3 13 Aging 5 14 Aging |
|
|
SqlStar
Posting Yak Master
121 Posts |
Posted - 2003-10-01 : 10:57:15
|
| its great.":-) IT Knowledge is power :-)" |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-10-01 : 13:05:24
|
| Are the ID columns identity columns? If so, why are you doing this? Why not use the natural key instead?Tara |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-10-01 : 14:01:03
|
| Category_id is needed because presumably the category name can change.You seem to be including the category hierarchy in the category name. I would have thought a ParentCategory_id would be better.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
bogey
Posting Yak Master
166 Posts |
Posted - 2003-10-01 : 22:09:40
|
Initial design was thisCategoryTable:---------------CategoryId (pk)CategoryDescriptionKeyWordTable:---------------KeyWordId (PK)CategoryID (FK)KeyWordDescriptionMy laptop battery is running low so I'll leave it as whats above. I'm assuming its ok, just trying to get some input if there is a better way.:)Danielquote:
Originally posted by nr
Category_id is needed because presumably the category name can change.You seem to be including the category hierarchy in the category name. I would have thought a ParentCategory_id would be better.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy.
|
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2003-10-01 : 22:33:32
|
| That design will not work. It only allows one category per keyword...It should be...Categories (Category) KEY (Category)KeyWords (KeyWord) KEY (KeyWord)CategoryKeyWords (Category, KeyWord) KEY (Category, KeyWord) -- And FK's to both tables...DavidM"SQL-3 is an abomination.." |
|
|
|
|
|
|
Need Help with Table Designs