There are some tables that I manage which contain a string value (for example, a 4 word "Type" field).
- There are only a few
- 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)