Is there a relational database engine that will automatically map code values?

Question

I have a table with lots of redundant data.

I'd like to change some of the VARCHAR values to some sort of "AUTO_LOOKUP" data type, that automatically maintains and resolves values from a look-up table.

MySQL does this partially with the ENUM datatype, but it requires ahead-of-time definition of all known values. I would like the list of values to dynamically grow.

Does this exist?

Related questions:

• Similar concept, using a custom datatype in Derby: User-defined types in Apache Derby as ENUM replacements

• Similar concept, but handling 100% in the client application (I want it handled in the database instead): Ways to save enums in database

Answer 1

Yes, it's called a foreign key, and it's supported by virtually all relational databases.

You define your set of varchars in a lookup table, with one row per value. Associated with the string is a more compact primary key, typically an auto-increment integer.

Then in your large table, just reference the entry in the lookup table by integer.