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?
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.
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...