I am working on developing a relational database that tracks transactions that occur on a device I'm working on for my company. There are different types of transactions that could occur on the device, so we have a "trans_type" field in one of our main record tables. My group has decided to make the type of this field an integer and treating it as an enumerated type. My intuition tells me that it would be a better idea to make this field a string so that our database data would be more readable and usable. My co-workers seem to be worried that this would cause more trouble than it is worth. That string comparisons are too costly and the possibility of typos is too great of a barrier.

So, in your opinion, when dealing with a field in a relational database that is essentially an enumerated value, is it a better design decision to make this field an integer or a string? Or is there some other alternative I've overlooked?

Note: explicit enumerated types are not supported by the database we are using. And the software we are developing that will interface with this database is written in C++.

  • Does it strike anyone else as long overdue to just make it a checked type definition in create table? Something like: CREATE TABLE hit(ip varchar(40), ip_class ENUM (0, "IPv4", 1, "IPv6")); It should allow you to check = < and > with either the ordinal or string (that maps to the ordinal).
    – dlamblin
    Sep 20, 2016 at 0:00

4 Answers 4


Enumerated types should be a separate table in your database that have an id number and a string name and any other columns you might find useful. Then each type exists as a row in this table. Then in your table you are recording the transactions the "trans_Type" field should be a foreign key to the key of that reference table. This is a standard practice in database normalization.

This way you have stored the one official name string, get to use number comparisons for performance, and have referential integrity that every transaction has a valid type.

  • 1
    Yes and if you decide you want to change 'O' to 'Open' you only have to change one row. Jan 9, 2013 at 20:30
  • +1. a simple int/string table is the best way to represent enums in a relational db.
    – mike30
    Jan 9, 2013 at 20:35
  • Probably, next visitors who are looking for Java solution find it useful
    – Jauhien
    Jan 14, 2015 at 15:14
  • 2
    This. For extra credit - if the dev team have defined the integers in an Java/C# enum or something similar, you can write a test that checks whether the code enum definition has diverged from the lookup table. There's always a danger that adding an element out of sequence can knock things out of sync and you don't realise until a live data record looks wrong. Jan 14, 2015 at 16:01

An common practice is to create a trans_types table, and then have your main table reference it with foreign key named trans_type_id. This ensures that your records will only reference valid enumerated types.



  trans_type_id (FK to trans_type.id)

Example Data:


1  |  SUBMIT
2  |  CANCEL


ID | trans_date  | trans_type_id
1  | 2012-12-31  | 1
2  | 2013-01-09  | 2

If the values are coming into the database as integers, store them that way. There's no need to put the over-head of converting to strings while writing to the database. You can always relate to a lookup table with the string/text values (More Normalized).

This has the added advantage of updating the string value in a single location instead of running some sort of update routine. Instead of 1 = 'Red' it could equal 'Really Red'

This is not ideal for reporting performance compared to just needing one table with string values (Denormalized). An index on this field would make performance good enough.

Most RDBMS will allow enough horsepower. Although your idea of being able to 'read' the table in its plain data form, joining a table is no big deal. Just get in the habit of using a view or some similar object.


I have to disagree with the other answers to this question advocating the separate enumeration table approach.

However, I certainly am in favor of not repeating what has already been said, so I'll simply refer to the accepted answer to (more or less) the same question on Stack Overflow: https://stackoverflow.com/a/229919/114626

  • +1 for the linked answer. For this question, your linked answer seems to be the correct one. But of course, if the questioner wants flexibility in the enumerated types then a reference table would be much better.
    – Harke
    Jan 4, 2019 at 20:34

Not the answer you're looking for? Browse other questions tagged or ask your own question.