Best practice to store enum via hibernate

Tags: #<Tag:0x00007f0f22a48778> #<Tag:0x00007f0f22a48570>

Hello!

in the radiologydcm4chee module class Study has a member performedStatus of type Integer which represents the DICOM Performed Procedure Step Status (with states “IN PROGRESS”, “DISCONTINUED”, “COMPLETED”). For now the member is stored in the database as Integer. I want to enforce type safety on that member by creating an enum with the three states. Something like this:

public enum PerformedProcedureStepStatus {
	
	IN_PROGRESS(0, "IN PROGRESS"), DISCONTINUED(1, "DISCONTINUED"), COMPLETED(2, "COMPLETED");
	
	final private Integer value;
	
	final private String displayName;
	
	PerformedProcedureStepStatus(Integer value, String displayName) {
		this.value = value;
		this.displayName = displayName;
	}
	
	public Integer getValue() {
		return this.value;
	}
	
	public String getDisplayName() {
		return this.displayName;
	}
}

I can see that in openmrs core enums are stored as varchar via HibernateEnumType

<property name="careSettingType" column="care_setting_type" length="50" not-null="true">
            <type name="org.openmrs.util.HibernateEnumType">
                <param name="enumClassName">org.openmrs.CareSetting$CareSettingType</param>
            </type>
 </property>

I dont want to use the enum ordinal to prevent being dependent on the enum order, thats why I added member value. Considering that in this case I only have 3 states, I think that in terms of reducing storage size in the db I’d rather store it as Integer (via value) or best something smaller than that instead of storing it as varchar (enum name).

Would you generally advocate for storing the enum name as is done in core? Would love to hear some thoughts :smile:

2 Likes

I think displayName should be a key to a resource properties file (if your module supports i18n).

1 Like

thanks @lluismf, good point! the module has message.properties which currently define the enum names as keys.

would you store the enum name or its member value as Integer in the db?

@teleivo, we have chosen to store the natural String value of the enum by convention within OpenMRS because (1) we get the translation between database value & enum “for free” without any additional mapping and (2) the values are more easily understood by someone exporting data from the database.

Your approach (storing Integer) is a valid approach as well. I agree with avoiding the ordinal value, since future changes in the code could change the meaning of previously stored data. It’s a matter of whether you think the benefits of storing an Integer (database size, performance) are significant enough to justify the cost (maintaining value mappings in code, obfuscating exported data).

Cheers,

-@burke

Personally I think that the additional clarity of having the varchar name stored far outweighs the space/performance improvements of storing an integer, which I presume are negligible.

1 Like

thanks a lot @burke and @darius for your take on this, I will think about it :slight_smile:

Having used both storage mechanisms (integer and varchar) over the years, I’ve now settled on using a varchar for enums. Part of me hates to frivolously use a varchar when an integer is so much more efficient but, as Darius mentions, working with an integer is just a lot more of a pain and the performance implications are tiny.

Also, in the past I’ve made SQL mistakes because I forgot which id matches with each enum. There is also the confusing way that hibernate stores enums by ordinal rather than a value. This, at least for me, was the unexpected default behavior I experienced and wasn’t something I figured out how to easily change.

I tend to use integers for this kind of entities when they must be localizable and the translation is in another table (the integer acts as PK in one table and FK in another). But I don’t have a strong argument for it.