0

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
5
  • 1
    One way to judge a schema is to write some of the queries that will need to use it. Let's see your SELECTs.
    – Rick James
    Oct 21 '21 at 4:33
  • Added some selects to the post
    – navig8tr
    Oct 21 '21 at 4:44
  • Won't those be joined to another table? That's usually the use of "lookup tables".
    – Rick James
    Oct 21 '21 at 4:49
  • Think about EAV instead of sparse table.
    – Akina
    Oct 21 '21 at 4:57
  • @RickJames Yes you are right, we will be joining on the various field types (type1 or type2) and selecting whichever field is needed.
    – navig8tr
    Oct 21 '21 at 5:38
0

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?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.