Database design...

[afranklin]

Hi,

Firstly, I hope this question makes sense and secondly that I don't get a prize for the worst question ever asked! Anyway... I'm using MS SQL and have three tables (simplified to make this er, simpler!):

Category
CatID (primary key)
CatName

SubCategory
SubCatID (primary key)
CatID (foreign key)
SubCatName

Item
ItemID (primary key)
ItemName

Each item belongs to a subcategory and each subcategory belongs to a category.

The question is: should my Item table contain a foreign key to SubCatID and CatID or just to SubCatID?

I think the answer should be just to have a foreign key to SubCatID because I can access the CatID through the SubCategory table but I wonder if there is any instance when you might also have the CatID in the item table (e.g. to make things quicker by needing less joins etc.)?

Thanks!

[tirna]

It is totally your choice depending on how you want to set up your data model.

If you want items to only be in sub categories then you only need a SubCatID column in the items table.

If you want both Categories and Sub Categories to be able to have items then you will need both a SubCatID and CatID column in the items table. But if you have this scenario you should make sure in your code that a ItemID cannot have both SubCatID and CatID values.

Another data model that might be worth considering is to have only 1 Cateogories table and that table could have a column which says for each category whether it is a main category (set that column value to 0) or a sub category where the column value would be the cat_id of it's main category. This could avoid an extra table in your db....anyway, just food for thought.

[ssystems]

As pointed out it depends on your data model

Since you are using MS SQL I would approach it this way.


Categories
CategoryId [UNIQUEIDENTIFIER] NOT NULL PRIMARY KEY
CategoryName [NVARCHAR] (VALUE) NOT NULL
ParentCategoryId [UNIQUEIDENTIFIER] NULL

Items
ItemId [UNIQUEIDEINTIFIER] NOT NULL PRIMARY KEY
CategoryId [UNIQUEIDENTIFIER] NULL (you may want an uncategorized item)
Item ....


This is a slight modification of tirna's suggestion. However, this scenario is only applicable to a 1-many relationship. For a many-many relationship (where an item can be categorized by multiple categories) a more scalable approach will be


Categories
CategoryId [UNIQUEIDENTIFIER] NOT NULL PRIMARY KEY
CategoryName [NVARCHAR] (VALUE) NOT NULL
ParentCategoryId [UNIQUEIDENTIFIER] NULL

Items
ItemId [UNIQUEIDEINTIFIER] NOT NULL PRIMARY KEY
Item ....

ItemCategories
ItemCategoryId [UNIQUEIDENTIFIER] NOT NULL PRIMARY KEY
ItemId [UNIQUEIDENTIFIER] NOT NULL
CategoryId [UNIQUEIDENTIFIER] NOT NULL



Now on your code behind you can just make an object Category, Item, IList<ItemCategory> relate to each other.