One lookup table per type or one lookup table to handle multiple types

Question

We are creating a lookup table that will handle multiple types of data. For simplicity, I'm only including 2, but there are actually 6. The table will store static data for each type.

We decided to put all the types in single table. The resulting data looks like the table below. But I'm wondering if it would be better to have multiple tables, one for each type.

When I suggested this, it was said that it would not make sense to have multiple tables with only a single row in each. Which I agree with, but having all these empty fields does not seem right either. Just wondering what is best practice?

type1	type2	type1_field1	type2_field1	type2_field2
first type		type1_field1_data
	second type		type2_field1_data	type2_field2_data

Some example queries:

SELECT type1_field1 FROM table WHERE type1 = 'first type'

SELECT type2_field1 FROM table WHERE type2 = 'second type'

SELECT type2_field2 FROM table WHERE type1 = 'second type'

  SELECT l.type1_field1, t.type_data
    FROM lookup_table l
        INNER JOIN type1_table t ON l.type1 = t.type

Answer 1

I don't know who said it was better to define one table that is the lookup table for everything, but on Earth-1, it's an antipattern so common that it has an acronym: OTLT (One True Lookup Table). Here's an article from 2005 warning about this antipattern: http://www.dbazine.com/ofinterest/oi-articles/celko22/

Think about your table if you were to need a third type. You'd need to add more columns, and the table would get longer. Suppose that third type didn't have just one row, but it's a lookup with many entries, like US postal codes or something. Suddenly your tiny lookup table would grow by 41,692 rows. Searches for the other two types stored in the same table would be hindered by the cost of searching a larger table (granted the columns should be indexed, but even indexed columns are affected by increasing the size of the table).

What is the cost of creating a separate table per type? Do you pay more for the MySQL Server software if you create more tables? No, you can create as many tables as are needed.

A table should represent a set of related rows. They belong together, because those rows are instances of a similar entity. If you mix entities of different types, you start to muddy some assumptions about what a relational table is.

For example, SELECT COUNT(*) FROM lookup_table doesn't do what it does in other tables. You would have to add conditions, or else SELECT COUNT(type1) FROM lookup_table and change which column you count when you want to know the count of rows for a different type.

Also, the table has no candidate key. The key column for each type must be nullable, therefore there's no column that can serve as the non-null unique column for a primary key. This also breaks the definition of a relational table.

Would you apply the same concept to your application code? For instance if you're a Java programmer, why not store all your Java variables in a single HashMap instead of in separate variables?