I have this relationship:
Many Objects to One Category
One Category to Many SubCategories
I am looking for the best way to store this in my Relational Database? Here were some of the ways I was thinking -
Option 1 (normalized)
Table 1 - Objects ObjectID int ObjectName NVarchar(256) Description NVarchar(256) Table 2 - Object Category Relationship CategoryRelationshipID int ObjectId int CategoryId int Table 3 - Categories CategoryID int CategoryName NVarchar(256) Table 4 - Object SubCategory Relationship SubCatRelationshipID int ObjectId int SubCategoryId int
Option 2 - Denormalized
Table 1 - Objects ID int Name NVarchar(256) Category NVarchar(256) SubCategory NVarchar(256) Description NVarchar(256)
Option 3 - Highly Denormalized
Table 1 - Objects ID int Name NVarchar(256) Category NVarchar(256) SubCategories NVarchar(256) <- concatenated array of strings for all of this object subcategories Description NVarchar(256)
Option 1 makes my head hurt with all of the join scenarios.
Option 2 could be a big table with and have redundant data for the many to many object to subcategory relationship.
Option 3 reduces the number of rows, but I am curious as to what troubles it brings (e.g. indexing, complexity of updating sub categories, complexity of queries on a specific sub category etc.).
Which one of these are the best structure?