SQLTeam.com | Weblogs | Forums

Multiple Category Tables vs One Master-Detail Category Table


#1

Suppose you have a big E/R database with many category tables like:
Sex
Type Id
Religion Type
Political View Type
.........
Whatever Type Id

What do you think is better?
Having multiple Category Tables or One Master-Detail Category Table?

I mean about the later, a Master Cat Tables like:
Table_Id. (ex: 1)
Name. (ex: 'TypeId')

And a detail table like:
ParentId. (ex: 1)
DetailId. (ex: 1)
Detail. (ex: 'Passport')

So, instead of having 20 or 40 category tables I 'm considering to have just one, what do you think about performance?


#2

I think a single table is less overhead to maintain and easier to use. If you can, stick to a smallint for the Table_Id, since that id will need to appear in every table row if you want to use referential integrity.

Also, keep in mind that you can use "sql_variant" data type for the Detail column, so you could even return different data types for each table id.


#3

I like to use a recursive lookup table like this:

Declare @Cat int;
CREATE TABLE tblValues(Id int , CategoryId int null references tlkpValues(Id), ShortValue varchar(15), LongValue varchar(50), Active bit, Ord smallint);
GO
INSERT tlkpValues(CategoryId, ShortValue, LongValue, Active, Ord)
VALUES (null, 'Sex', null, 1, 1);
SET @Cat=SCOPE_IDENTITY();
VALUES (@Cat, 'Male', null, 1, 1)
            ,  (@Cat, 'Female', null, 1, 2);
--And so on...