One lookup table or multiple lookup tables?

I have struggled with the same question myself. If the only thing in the lookup table is some kind of code or id and text value, then it certainly works to just add an "attribute id" and dump it all into one table. The obvious advantage is that you only have one table to create and manage. Searches can be slower because there are more records to look up, but presumably you are creating an index on id id id id. At this point, whether performance is better with one large table or ten small tables probably depends on all sorts of details about how the database engine works and about the access pattern. This is a case where I would say: if in practice this is not a problem, don't worry about it.

Two caveats:

One. If you are creating a separate table I would create a code for the attribute name and then another table to display the codes. How:

lookup_attribute(attribute_id, attribute_name)
lookup_value(attribute_id, value_id, value_text)

      

        
        
        
      

    

Then the first table has records like

1, 'Gender'
2, 'Marital Status'
3, 'Education'
etc

      

        
        
        
      

    

And the second

1, 1, 'Male'
1, 2, 'Female'
1, 3, 'Undecided'
2, 1, 'Single'
2, 2, 'Married'
2, 3, 'Divorced'
2, 4, 'Widowed'
3, 1, 'High School'
3, 2, 'Associates'
3, 3, 'Bachelors'
3, 4, 'Masters'
3, 5, 'Doctorate'
3, 6, 'Other'
etc.

      

        
        
        
      

    

(The value identifier can be unique across all attribute identifiers, or it can only be unique within the attribute identifier, no matter what works for you. It doesn't matter.)

Two. If there is other data that needs to be stored for some attribute besides the value text, then split it into a separate table. For example, if you have an attribute like "Membership Level" and then the user says there are different contributions for each level and you need to record that, then you have an additional field that only applies to that one attribute. At this point, it should become its own table. I have seen systems where they have some extra data for each of the multiple attributes and they create a field called "extra data" or some of them, and for "membership level" it contains the annual fees and "store name" "it contains the city in which the store is located and for the "item number"it contains the number of units at hand for that item, etc., and the system quickly becomes a management nightmare.

Update

To get the values, let's assume we only have gender and marital status as searches. The principle is the same for others.

So, we have a monster lookup table as described above. Then we have a member table, say

member (member_id, name, member_number, whatever, gender_id, marital_status_id)

      

        
        
        
      

    

To receive a message, just write

select m.member_id, m.name, m.member_number, m.whatever,
  g.value_text as gender, ms.value_text as marital_status
from member m
join lookup_value g on g.attribute_id=1 and g.attribute_value=m.gender_id
join lookup_value ms on ms.attribute_id=2 and ms.attribute_value=m.marital_status_id
where m.member_id=@member_id

      

        
        
        
      

    

Alternatively, you can:

member (member_id, name, member_number, whatever)
member_attributes (member_id, attribute_id, value_id)

      

        
        
        
      

    

Then you can get all w attributes

select a.attribute_name, v.value_text
from member_attribute ma
join lookup_attribute a on a.attribute_id=ma.attribute_id
join lookup_value v on v.attribute_id=a.attribute_id and v.value_id=ma.value_id
where ma.member_id=@member_id

      

        
        
        
      

    

It seems to me that I'm trying to write queries that have a clear advantage to make the id of the value globally unique: this not only removes the need to specify the id of the attribute in the connection, but it also means that if you do, there is a field, say gender_id, you may still have a foreign key position.