SQLTeam.com | Weblogs | Forums

Best DB Design between two

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

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.


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.

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.

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

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

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.