SQLTeam.com | Weblogs | Forums

Best DB Design between two


#1

I have two tables, a Department and a Category, both only have 3 fields.
Id (PK),
Name,
Description.

These two tables join together in a 1 to many, Department to Category, and that is where my question is.

Should I create a 3rd table with 3 fields, Id, Department_Id, Category_Id, for joining the 2 tables together. This keeps Category from having duplicate records if it is in more than 1 department.

Or

Add a Department_Id into Category table, and have it have multiple category records if it is in more than one department?

The dataset is small, 100 or so records.


#2

It sounds as if it's many to many rather than 1 to many.

In that case, you must use a 3rd table. But that table should NOT have a separate id column. The key for that table should be ( department_id, category_id ) or ( category_id, department_id ), the order depending on how you use / lookup the data.


#3

Thanks for the response, but it is a 1 to many relationship.


#4

If a given category can only be in one department, then add a department_id column to the category table.


#5

Yeah, I agree and decided to go that route. I think the other is only good if there are tones or records or many to many.