Sign up ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free.

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++.

share|improve this question

3 Answers 3

up vote 17 down vote accepted

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.

share|improve this answer
Yes and if you decide you want to change 'O' to 'Open' you only have to change one row. – Daniel Kaplan Jan 9 '13 at 20:30
+1. a simple int/string table is the best way to represent enums in a relational db. – mike30 Jan 9 '13 at 20:35
Probably, next visitors who are looking for Java solution find it useful – Jauhien Jan 14 at 15:14
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. – Julia Hayward Jan 14 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

Example Data:


1  |  SUBMIT
2  |  CANCEL


ID | trans_date  | trans_type_id
1  | 2012-12-31  | 1
2  | 2013-01-09  | 2
share|improve this answer

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.

share|improve this answer

protected by gnat Jan 14 at 15:10

Thank you for your interest in this question. Because it has attracted low-quality answers, posting an answer now requires 10 reputation on this site.

Would you like to answer one of these unanswered questions instead?

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