CODEWITHSUNDEEP
databasehibernateenumsforeign-keys
Andreas Schilling
Andreas Schilling

Reputation: 95

Use Enums instead of foreign keys

After using Hibernate Enum Mapping again today I was wondering about whether it would be a good idea to stretch it a little bit more.
To explain where my thought came from: of course we aim for a normalized data model for our applications which often results in the fact, that we get alot of tables that contain something like a category, a state or similar data. Usually these tables have very few columns (often only PK and 1 or 2 content columns) and rows. Also, the content of those tables changes very rarely, sometimes even never.
If we'd use an enum for that and map it to the table by Ordinal or by an Integer (both with Hibernate, but I'd say any ORM can do that), wouldn't that be better in both performance (less joins) and handling (enums in Java can be used very elegantly)?

To clarify things a bit:

Table PERSONS
ID: Number
NAME: Varchar
RELATIONSHIP_STATUS_ID: Number

Table RELATIONSHIP_STATUS
ID: Number
STATUS: Varchar

Content PERSONS:
1 | John Doe | 1
2 | Mary Poppins | 2

Content RELATIONSHIP_STATUS
1 | Single
2 | Married

Now I'd dump the status table, have those two status in an enum and map that to the column by ordinal.
Would this be a senseful thing to do? I especially would be interested if this kind of design would be better performance-wise.

Upvotes: 1

Views: 771

Answers (1)

JB Nizet
JB Nizet

Reputation: 692053

My factors for choosing between a table and an enums are the following:

  • the list of possible values could change in the future, and we don't want to recompile, retest and redeploy the app when it happens: we use a table
  • the list of possible values could change in the future, but every value of the table is used in the code itself to implement some business logic (like if status == married then do something else do something): we'll need to change the logic anyway if the list of possible values change, so we use an enum
  • the list will never, ever change: we use an enum

You can still keep the table, and use an enum in the code, though. This makes it clearer when just looking at the data in the database, when you don't know how the enum is implemented. 0 meaning married and 1 meaning single is not obvious at all. If you keep the table just for reference, you can at least figure what the values mean, and make sure that it's not possible to insert 2 or any other number in the data.

Another way is to use the name of the enum rather than its ordinal. It takes up a bit more space and is a bit less efficient, but it makes the data even clearer and simpler to analyze. You lose the safety, though, unless you add a checked constraint.

Upvotes: 2

Related Questions