CODEWITHSUNDEEP
javasqldatabaseenumsmodeling
arthur
arthur

Reputation: 3325

How to model a (Java) Enum in a Database (with SQL92)

Hi am modelling an Entity with a column named "Gender". In the application code, the gender is supposed to be an (Java) Enum type, with 2 values (MALE and FEMALE).. how would you model it, knowing that Enums as Datatype are not part of the generic SQL (language) 92.

It the data model would have to be portable, in order to be used by several DBMS (Mysql, Oracle, Derby) and several persistence provider (JPA, Hibernate, JDBC). I feel, it hits at 2 levels: - first the level of the Schema and the entity (store the enums as -VAR-CHAR or INT) - second the level of the app code (JDBC doesn't support enums, JPA does but it depends on the implementation)

Is there a generic solution, applicable for example to an Mysql DBMS ? Thanks !!

Upvotes: 2

Views: 3527

Answers (2)

Thomas
Thomas

Reputation: 88707

Enums have at least two properties by which they can be identified: an ordinal (the position of the instance in the enum list) and a name. Both could be stored in the database and be used to retrieve the correct enum instance.

If you're using an OR-mapper like Hibernate it'll do that for you, otherwise you need to manually convert the enum to an integer/long or a string and vice versa.

To make it safer you could add a custom property to your enum and provide a getter gor the property as well as aconversion function for property->enum value. That way you're free to reorder and rename the enum values as long as you don't change that id property.

Upvotes: 1

Adamski
Adamski

Reputation: 54695

I typically adopt the following solution, which gives a fully normalised database schema.

  1. Create a dedicated table to represent your enumeration; e.g. Colour. The table will have two columns:
    • An ID column (primary key). Typically an auto-incrementing int, smallint or tinyint.
    • A name column (candidate key). This will be a varchar whose value should be identical to the Java enumeration.
  2. Create a Java enum: Colour with identical values to the Colour table in the database.

  3. Assuming you now have a "Data" table containing records that reference the Colour table, implement a stored procedure for inserting a record, which takes the Colour String value (as described by your Java enum); e.g.

    CREATE PROCEDURE dbo.InsertRecord @colour varchar(32) AS ...

The stored procedure will perform the necessary String-to-int translation allowing your data table to reference a the Colour table via an int foreign key.

Similarly you can create a view onto your data table(s) that will join to the Colour table behind the scenes to present a denormalised via of your data back to the application or end user.

The alternative to the stored procedure approach is that you read and cache the ordinal, name mappings within your application on startup. This has the advantage of being able to verify the integrity of your Java Colour enum compared with the contents of your Colour table in the database.

Upvotes: 2

Related Questions