Why should I use lookup tables at all?

Question

This question confirmed, what I was already aware of -- instead of directly storing strings in table, I should rather use integers or enums and join them with lookup table, to get, as a result, string, that I actually want.

But, why should I use lookup at all? If I'm getting an integer from base table, what prevents me (or what should prevent me) from loading lookup values from file (for example an associative array stored in text file or directly in .php file, in case of PHP) and using them this way? Isn't that faster?

I'm trying to make this question general (that's why asking on Programmers, not on SO), but since I'm PHP programmer, PHP-based example is the only that comes to my mind. Assume, that I have a file containing:

<?php
    return array
    (
        '1'=>'male',
        '2'=>'female',
        '3'=>'unspecified',
        '4'=>'unknown'
    );
?>

Why shouldn't I use it as my "lookup solution", instead of replicating the same dataset in lookup table?

Answer 1

There are a number of reasons why you want to store you lookups in a database.

1. Referential integrity. If you set up the database correctly you CANNOT insert a row unless the lookup exists (foreign key constraint). There is currently nothing stopping me inserting integer 0 or 9999 and your array lookup will fail.

2. Cascading deletes & orphan dependencies. Related to point 1. If I delete #3 from the lookup table I would expect either cause an error because parent rows still reference #3, or referenced rows to be deleted, or the foreign key reference to be nulled. If any of these things don't happen you now have dangling references in your database!

3. Easier to change. If you need to change 1 & 2 to be 'man' & 'woman' you would need to rebuild and redistribute your code. If it's in the database, just update the lookups. You will have a similar problem when it comes to add new genders.

4. Extra complexity - to find all your male people, you need to look up what 'male' equates to in your array, then include that value in a query you send to the database. If everything is stored in the database it can be performed in one query and optimized by the query engine:

   SELECT people.id, people.name, genders.name FROM people INNER JOIN genders ON peoples.gender_id = gender.id WHERE genders.name = 'male'

5. If the data's not in the database you can't do databasey things with it! What if your client needs the list of people ordered by gender name? If it's in a lookup table, it's as simple as:

   SELECT people.id, people.name, genders.name FROM people INNER JOIN genders ON peoples.gender_id = gender.id WHERE genders.name = 'male' ORDER BY genders.name ASC

Answer 2

You are suggesting a client-side join: Fetch some data from the database via a query, and then in code you perform further joins via lookup tables loaded from files.

This is almost certainly going to be slower, and definitely more complex than just doing the join in the database. Think about it - why should you be able to write you own join-logic to be faster than what happens in a database? Unless you have very specific requirements (e.g you want to minimize the amount of data returned from the database) I don't see any advantages to performing the join on the client side.

Answer 3

I think you are confusing a few different things here.

1: database normalisation.

The question answer you link to is about normalizing out repeated data into a second table with a FK. The key needent be an int and will be repeated, so if your data is JUST the key you are already normalized.

2: ints as keys (due to space?)

You suggest that using an int is better as a key, but this is contaversial. GUIDs are another option and strings can be used.

3: persisting enum values

So an enum will eval as an int, but if you only store the int -> name relationship in your code and not the db you have potential issues if you ever change the enum.

If you replicate the enum in a lookup table then you double up on this problem and will need some way of syncing code with db.

Its usualy best to persist an enum as a string, as this carries the meaning and an enum is not a 'display string' that will change, be translated etc

So taking these together, where you have a poco object with an enum field. i recommend storing the field as a string in a db table with cols which match the objects properties. Ie

Person

Id -> some guid

Name -> fred

Sex (ENUM) -> male

Where the property is a class with more than one property you should use a FK to another table. Ie.

address (class) -> (addressId col) some guid

If you treat the enum like a class and move it out to a lookup table you can achieve space savings if you have many rows and few enum values Eg.

Sex(enum) male -> (sexId [short] ) 0

Sex

id [short]

Name [varchar(50)]

But the id and FK relationship should be purely at the DB layer. You shoud still return the Name from queries and deserialise that string back to your enum rather than mapping the short Id to your enum int.

However, this is an optimisation rather than a normalisation and you are only saving space, which as your referenced question says, is cheap. Personally I tend to leave this kind of thing to DBAs

Consider for example you will need an insert sproc, which takes the enum string, checks the lookup table to find the short id, adds the enum to the lookup table if required and then uses the short id for the insert. So there is a downside to the optimisation as well as an upside