Category/Sub Category relationsips

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

Category/Sub Category relationsips

Author

Topic

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2009-07-27 : 11:57:37

Dear DB experts:

I am tasked with associating one category with 3 different sub categories.

For instance,

Category has the following with their sub category associations

CATEGORY

PUA
sub category1 sub category2
1st Attempt 10 Hours
2nd Attempt 16 Hours
3rd Attempt 22 Hours
... etc
...
up to 11 attempts

MUA
sub category1 10 Hrs WD
1st MUA 5Day Term
2nd MUA 6Day Term
3rd MUA ...
... ...
... Other
Other

There are a total of 11 Categories and each category has its own sub category.

Also, the values for sub category2 are same as sub category3.

Now, I know I can create a lookup table called Category and put the 11 categories into this table.

I can create a table for subCategory1 and put the values of subCategory1 into this table.

I can also create a table called subCatgory2And3 and put the values of subCategory2 into this table and use this one table for subCategory2 and subCategory3 values.

My issue is how do I relate Category table to the subCategory tables in such that if I create a dropdown and select say, PUA, sub category1
will be populated with ist Attempt, 2nd Attempt, 3rd Attempt values and subCategory2 and subCategory3 will be populated with 10 Hours
16 Hours, 22 Hours, etc values?

What do I need to do to have a normalized relationship between Category and sub categories?

Thanks a lot in advance

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-07-27 : 13:10:50

Not sure I'm following you but perhaps these two tables. This way you can associate any number of subcategories with the appropriate category. And mulitiple categories can share the same subcategory if needed.


use tempdb
go
create table [category] 
       (categoryid int primary key clustered
       ,categoryDesc varchar(50))
go
create table [subCategory] 
       (categoryid int references category(categoryid)
       ,subCategoryDesc varchar(50)
       ,primary key clustered (categoryid, subCategoryDesc))
go

drop table subCategory
drop table Category

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources