Persisting Enums in database tables

Question

I have an order which has a status (which in code is an Enum). The question is how to persist this. I could:

Persist the string in a field and then map back to enum on data retrieval.
Persist this as an integer and then map back to enum on data retrieval.
Create separate table for enum value and do a join on data retrieval.

Thoughts?

A nice feature of #1 is that, if the DB does get out of sync with the app, at least you know what value you meant to store, and it would be easy to fix the DB if the string value of the enum was changed in .NET. — mbeckish, Mar 23 '09 at 16:35

score 8 · Accepted Answer · answered Mar 23 '09 at 16:27

8

If this is a fixed list (which it seems it is, or else you shouldn't store it as an enum), I wouldn't use #1.

The main reason to use #3 over #2 is for ease of use with self-service querying utilities. However, I'd actually go with a variant of #2: Store the value as an integer and map to an enum on data retrieval. However, also create a table representing the enum type, with the value as the PK and the name as another column. That way it's simple, quick, and efficient to use with your code, but also easy to get the logical value with self-service querying and other uses that don't use your data access code.

answered Mar 23 '09 at 16:27

Jonathan Rupp

15,522
5
45
61

1

+1, I'd also setup an FK to ensure that the integer is a valid enum value. – LukeH Mar 23 '09 at 16:44
Yes, plus, having it as an FK helps with discoverability for self-service query tools. – Jonathan Rupp Mar 23 '09 at 17:54

score 3 · Answer 2 · answered Mar 23 '09 at 16:26

3

#3 is the most "proper" from a database/normalization standpoint. Your status is, in effect, a domain entity that's linked to the order entity.

answered Mar 23 '09 at 16:26

Harper Shelby

16,475
2
44
51

score 0 · Answer 3 · answered Mar 23 '09 at 16:25

0

hibernate uses integers by default. if your enum is not going to change very often, this is not a bad idea, i think.

answered Mar 23 '09 at 16:25

PeterP

4,502
7
22
21

score 0 · Answer 4 · answered Mar 23 '09 at 16:26

0

I'd use an integer mapped to value in another table with the values. You could also then map the enum to the same value, but then you'd have to update in both spots.

answered Mar 23 '09 at 16:26

sfossen

4,774
24
18

score 0 · Answer 5 · answered Mar 23 '09 at 16:26

I suppose it depends on where the data will be retrieved. With #3, you could retrieve the data without relying on your .NET front end. But it is also possible for your database table to get out of sync with the enum code.

Option #2 is certainly the most efficient way to do it for storage... but storage is cheap.

Persisting Enums in database tables

5 Answers5

Linked

Related