SQL 2005 Database Design

table: category
columns: id, name

table: item
columns id, name

table: item_categories
columns: id_item, id_category

table: category_categories
columns: id_category_parent, id_category_child

you could eventually "merge" the last table into the categories table directly, by this the table would self-reference itself.

Something like this:

Items table with ItemID as primary key
Categories table with CategoryID as primary key
SubCategories table with SubcategoryID as primary key and CategoryID as a foreign key
ItemsCategories table with ItemID and CategoryID as the primary key and foreign key constraints to both the Items and Categories tables.

This gives you a many-to-many relationship between Items and Categories and a one-to-many relationship between Categories and SubCategories.

Greg

If I understand you correctly, the following is try:
Item => Category is 1 to Many
Item => SubCategory is 1 to (0/many)
So, how do Category and SubCategory relate?  Are these totally independent or are certain SubCategories allowed uner specific Categories?
If Category => SubCategory as 1 to (0/Many) then you have one situation.  If not, then you have an entirely different one.
I am going to present 3 alternatives, one for the SubCategories Totally Dependent upon Categories, one for the SubCategories Partially Dependent upon Categories and one for them being totally independent.

Partially Dependent Scenario:
 
Items Table
ItemID NOT NULL
ItemName NOT NULL
CategorySubcategoryID NOT NULL
 
Category Table
CategoryID NOT NULL
CategoryName NOT NULL
PK CategoryID 
 
SubCategory Table
SubcategoryID NOT NULL
SubcategoryName NOT NULL
PK SubcategoryID
UNIQUE KEY SubcategoryName 
 
CategorySubCategory Table
CategorySubCategoryID NOT NULL
CategoryID     NOT NULL
SubcategoryID  NULL
PK CategorySubCategoryID 
UNIQUE KEY SubcategoryID, SubcategoryID 
 
Totally Dependent Scenario
 
Items Table
ItemID NOT NULL
ItemName NOT NULL
SubcategoryID NOT NULL
PK ItemID 
 
Category Table
CategoryID NOT NULL
CategoryName NOT NULL
PK CategoryID 
 
 
SubCategory Table
SubcategoryID NOT NULL
CategoryID NOT NULL
SubcategoryName NULL
PK SubcategoryID
UNIQUE KEY CategoryID, SubcategoryName
 
 
Totally Independent Scenario
 
Partially Dependent Scenario:
 
Items Table
ItemID NOT NULL
ItemName NOT NULL
CategoryID NOT NULL
SubcategoryID NULL
 
Category Table
CategoryID NOT NULL
CategoryName NOT NULL
PK CategoryID 
 
SubCategory Table
SubcategoryID NOT NULL
SubcategoryName NOT NULL
PK SubcategoryID
UNIQUE KEY SubcategoryName 

Select allOpen in new window

Thanks for your responses;
8080_Diver: To answer your question, the SubCategory are specific to each category;  so a SubCategory only relates to one Category.  

As well, the depth is only 1 subcategory deep (and will always be)...

If you could provide SQL code to create these tables, that would be much appreciated.

Please note: I have not tested the following SQL (no test-bed SQL Server available ;-) but it should be close.
The other "gotcha" is that you will need to handle the constraint on making sure that every Item has at least 1 Categorization assigned to it.  trying to put constraints on to accomplish that starts getting you into circular constraints.

--Totally Dependent Scenario
 
-- ItemCategorization => Categorization => SubCategory
-- ItemCategorization => Items
-- Items => (1 to Many) ItemCategorization => (1 to Many) Categorization => (1 to 0/Many) SubCategory
 
 
CREATE TABLE Items
(
 ItemID           INT IDENTITY (1,1) PRIMARY KEY, 
 ItemName         VarChar(50) UNIQUE NOT NULL,
);
 
 
CREATE TABLE Categorization
(
 CategorizationID INT IDENTITY (1,1) PRIMARY KEY,
 CategoryName     VarChar(50)  NOT NULL,
 SubCategoryID    INT NULL
);
 
 
CREATE TABLE SubCategory
(
 SubCategoryID 		INT IDENTITY (1,1) PRIMARY KEY,
 SubCategoryName  VarChar(50) UNIQUE NOT NULL,
);
 
 
CREATE TABLE ItemCategorization
(
 ItemID           INT NOT NULL,
 CategorizationID INT NOT NULL
);
 
CREATE PRIMARY KEY PK_ItemCategorization
ON ItemCategorization(ItemId, CategorizationID);
 
 
CREATE UNIQUE INDEX IX_Categorization_Subcategory 
ON Categorization(SubcategoryName, SubCategoryID) 
INCLUDE (CategorizationID ) WITH IGNORE_DUP_KEY;
 
ALTER TABLE Categorization
ADD CONSTRAINT FK_CategorizationSubCategoryID FOREIGN KEY (ItemID) 
REFERENCES ITEM(ItemID);
 
 
ALTER TABLE Item
ADD CONSTRAINT FK_ItemCategorization FOREIGN KEY (CategorizationID) 
REFERENCES Categorization(CategorizationID);
 
ALTER TABLE Item
ADD CONSTRAINT FK_ItemCategorization FOREIGN KEY (CategorizationID) 
REFERENCES Categorization(CategorizationID);
 

Select allOpen in new window

JestersGrind:

When I ran the SQL code, an error stating:

Msg 8111, Level 16, State 1, Line 9
Cannot define PRIMARY KEY constraint on nullable column in table 'ItemCategories'.

8080_Diver:

When I ran your code, an error stating:

Msg 156, Level 15, State 1, Line 29
Incorrect syntax near the keyword 'KEY'.
(which is the CREATE PRIMARY KEY PK_ItemCategorization
ON ItemCategorization(ItemId, CategorizationID); line


I'm sure these are easy fixes; but I SQL and I are not compatible...

Also, assuming that both SQL scripts were working; which one would be the best?
Thanks again for your time;