Is there such thing as automatic ENUMs? There are some tables that I manage which contain a string value (for example, a 4 word "Type" field).


There are only a few DISTINCT strings, however
the application may insert new strings that have never been seen before, but
in the life of the application, there will never be more than 200 strings, which means the system could store this field in a single byte.


Disk space is not an issue, but the less bytes, the faster the lookups.

I have this idea that I doubt any database engines are supporting, but I wanted to check with the experts.

The strings could be mapped to IDs in a separate table, and that way you would not need to repeat these strings on every row of the primary table. Ideally the database would manage this internally for you. The unique strings would be stored as part of the table definition, invisibly, internally, and the system would work great, no need to modify the app, for all string fields that are below 255 distinct possibilities.

Is there anything like this on any database engine? I mostly use MySQL, but I am curious if this has ever been implemented, short of modifying the applications call a custom wrapper function. (like INET_ATON, except that it would transparently do an INSERT to an AUTO_INCREMENTing table to get the ID for the first time)
Maybe I'm missing something but why wouldn't you just use a lookup table with a foreign key relationship for this sort of thing potentially with some views to provide a layer of abstraction?  That's a very common approach, it's supported by all databases, it's pretty easy to implement, and it solves other issues such as data consistency (i.e. what happens if you want to update an existing string without updating every row in the table that has that string).  If you know the maximum number of entries in the lookup table, you can pick the data type that minimizes data volumes for the primary key.  That's also much easier to handle in the future when someone decides that the number of distinct values needs to exceed whatever limit you specified initially-- you just need to increase the size of the column in both tables.