One lookup table or many lookup tables? [closed]

Question

I need to save basic member's data with additional attributes such as gender, education, profession, marital_status, height, residency_status etc.

I have around 15-18 lookup tables all having (id, name, value), all attributes have string values.

Shall I create member's table tbl_members and separate 15-18 lookup tables for each of the above attributes:

tbl_members:

mem_Id
mem_email
mem_password
Gender_Id
education_Id
profession_id
marital_status_Id
height_Id
residency_status_Id

or shall I create only one lookup table tbl_Attributes and tbl_Attribute_Types?

tbl_Attributes:

att_Id
att_Value
att_Type_Id

Example data:

001 - Male - 001
002 - Female - 001
003 - Graduate - 002
004 - Masters - 002
005 - Engineer - 003
006 - Designer - 003

tbl_Attribute_Types:

att_type_Id
att_type_name

Example data:

001 - Gender
002 - Education
003 - Profession

To fill look-up drop-downs I can select something like:

SELECT A.att_id, A.att_value, AT.att_type_name
FROM tbl_Attributes A
INNER JOIN tbl_Attribute_Types AT ON AT.att_type_Id = A.att_type_Id
WHERE att_Type_Id = @att_Type_Id

and an additional table tbl_mem_att_value to save member's attributes and values

tbl_mem_att_value:

mem_id
att_id

Example data for member_id 001, is Male, Masters, Engineer

001 - 001
001 - 004
001 - 005

So my question is shall I go for one lookup table or many lookup tables?

Thanks

Answer 1

Never use one lookup table for everything. It will make it more difficult to find things, and it will need to be joined in every query probably multiple times which will mean that it may cause locking and blocking problems. Further in one table you can't use good design to make sure the data type for the descriptor is correct. For instance suppose you wanted a lookup of the state abbreviations which are two characters. If you use a onesize fits all table, then it has to be wide enough for teh largest possible value of any lookup and you lose the possibility of it rejecting an incorrect entry because it is too long. This is a guarantee of later data integrity issues.
Further you can't properly use foreign keys to make sure data entry is limited only to the correct values. This will also cause data integrity issues.

There is NO BENEFIT whatsoever to using one table except a few minutes of dev time (possibly the least important concern in designing a database). There are plenty of negatives.