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)

  • The closer I can think to this "automatic" look-up table is what some column-oriented products like Infobright do. – ypercubeᵀᴹ Jun 9 '12 at 10:45

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.

  • Is a foreign key going to automatically add new types as the appear, or do I need to program my application to do this? Maybe an example would clear things up, for a many-row table with a type column, and then the types are only a few, but not all predetermined, how would this solution work? – Bryan Field Jun 9 '12 at 0:17
  • @George: yes, you need to adjust your application - or your db procedures. – ypercubeᵀᴹ Jun 9 '12 at 10:46

Disk space is not an issue,


in the life of the application, there will never be more than 200 strings,

Famous last words. I'm always happy to bet against this kind of requirement, because I know I'll win a lot more often than I'll lose.

The industry standard way to handle this is with a separate table (often called a lookup table) and a foreign key reference to it. Some database platforms support emun data types, but they seem inferior to lookup tables when it comes to maintenance. Changing enums requires altering the structure of a table; changing data in a lookup table is just changing a row.

Application code that needs to present the strings to a user queries the lookup table for the strings and their id numbers or their codes in sorted order, then loads the result set into a combo box, listbox, or whatever. Any strings added to that table will automatically appear in subsequent queries, unless you're using a cache that doesn't update automatically.

  • It is rather hard to deny that it will never reach 200, if in the first 5 years it hasn't even reached 20 yet. (contrived example based on real experiences) I agree that the separate table is a good idea, but I was hoping that a separate table could be created and updated automatically by the database. If my idea was to be implemented, it would avoid updating all the existing application code. Just to clarify, this is a minor improvement, and an idea of mine, no problems need to be solved at this time. – Bryan Field Aug 16 '12 at 23:31
  • Where does your application code get the options it presents to the user? – Mike Sherrill 'Cat Recall' Aug 17 '12 at 0:26
  • There is no such code. The user does not directly create new options. The application treats this column as a varchar, and just so happens to only use a few distinct values. Most of the new values come from changes to the application, but some come from other events. The idea is to make the field look like a varchar, while still only requiring a single byte of storage. – Bryan Field Aug 18 '12 at 15:59
  • Nothing automatic for that on any SQL platform. MySQL has an enum, but it's not automatic. ("Automatic" would require altering the definition of the table at run time. That could be computationally very expensive.) – Mike Sherrill 'Cat Recall' Aug 18 '12 at 17:57
  • "'Automatic' would require altering the definition of the table at run time" You already said that, and I already knew that. Maybe you should read my comments. I already said "I was hoping that a separate table could be created and updated automatically by the database" "The strings could be mapped to IDs in a separate table, ... internally.". – Bryan Field Aug 18 '12 at 18:40

Column Compression such as Oracle's Hybrid Columnar Compression satisfies your goal of fewer bytes for faster lookups. It also has the benefit of decreasing disk space, though not as much as the automatic enum you describe. On the other hand, it can handle far more than 200 distinct values and requires no application side changes.

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.