October 22, 2010 at 7:05 am
Hi,
I have an entity named Item that has following attributes:
Item:
Name,
Category,
Subcategory
The database should enforce the following rules:
1.An item can belong to one of 3 categories;
2.A category can have zero or more subcategories.
For example:
Category1 has 3 subcategories: A, B, C;
Category2 does not have subcategories;
Category3 has 2 subcategories: F and G.
How can I model this in order to enforce these rules through design?
October 22, 2010 at 7:38 am
ioani (10/22/2010)
Hi,I have an entity named Item that has following attributes:
Item:
Name,
Category,
Subcategory
The database should enforce the following rules:
1.An item can belong to one of 3 categories;
2.A category can have zero or more subcategories.
For example:
Category1 has 3 subcategories: A, B, C;
Category2 does not have subcategories;
Category3 has 2 subcategories: F and G.
How can I model this in order to enforce these rules through design?
1- Establish a 1-to-1 relationship between Item and Category tables on Category column.
2- Do nothing, it will auto-enforce.
Hope this helps.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.October 22, 2010 at 8:02 am
I have following tables:
CREATE TABLE Item
(
ItemID INT,
Name VARCHAR(60) ,
CategoryID TINYINT,
Subcategory
)
CategoryID from Item is a foreign key to CategoryID from Category.
CREATE TABLE Category
(
CategoryID TINYINT,
Description VARCHAR(60)
)
I have a stored procedure that inserts items and has as arguments @ItemName, @Category, @Subcategory.
When an item is inserted of one category, it should also have a valid subcategory.
So, if someone want to insert an item with Category1 and Subcategory F (see my example) the stored procedure should raise an error because Category1 does not have Subcategory F.
Can be done this through design without writing code in stored procedure that verify the validity of the subcategory?
Thank you,
ioani
October 22, 2010 at 12:47 pm
ioani (10/22/2010)
I have following tables:
CREATE TABLE Item
(
ItemID INT,
Name VARCHAR(60) ,
CategoryID TINYINT,
Subcategory
)
CategoryID from Item is a foreign key to CategoryID from Category.
CREATE TABLE Category
(
CategoryID TINYINT,
Description VARCHAR(60)
)
I have a stored procedure that inserts items and has as arguments @ItemName, @Category, @Subcategory.
When an item is inserted of one category, it should also have a valid subcategory.
So, if someone want to insert an item with Category1 and Subcategory F (see my example) the stored procedure should raise an error because Category1 does not have Subcategory F.
Can be done this through design without writing code in stored procedure that verify the validity of the subcategory?
Yes. Referential Integrity will do the trick.
All you need is a SubCategory table.
Model should look like...
Item (1) <===> (1) Category (n) <<===> (1) SubCategory
Meaning...
- Each row in Item table has to have a parent key in Category table.
- Each row in Category table has to have a parent key in SubCategory table.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.October 24, 2010 at 11:53 pm
It is a nice solution.
Thank you,
ioani
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply