Reference Table OR Java Enum which is better

Question

I have a database entity: worker, and there are essentially two types of workers,

Full-Time
Part-Time

I am certain that this list of types won't grow frequently.

id	name	worker_type_id
1	Adam	1
2	Milne	2

public class Worker{
 private int id;
 private int name;
 private WorkerType type;
}

For Worker_Type I have two options:

Create a Java ENUM of WorkerType

public enum WorkerType{

 WorkerType(int id, String name){
  this.id = id;
  this.name = name;
 }

 int id;
 String name;
}

Create a reference table that stores WorkerType.

worker_type_id	worker_type_label
1	Full Time
2	Part Time

I am inclined towards using "1" but I see a lot of people speak in favour of "2". Apart from the obvious comparisons, how in code would I query all Full-Time workers in the worker table?

select * from worker join worker_type on worker.worker_type_id = worker_type.id where worker_type = 'Full Time';

My question about the above query is how would my code know about the string "Full Time"? If someone mistakenly changed the label it would break the code completely. How do I handle this?

user_642706 · Answer

Your example code:

public enum WorkerType{

 WorkerType(int id, String name){
  this.id = id;
  this.name = name;
 }

 int id;
 String name;
}

… confuses your question. You are mixing your worker type with your actual workers.

Likely the code appropriate to your situation is a class to represent each worker.

public record Worker( int id , String name , Classifation classifation ) {}

The type of worker is a separate enum, with only two named objects (one for full-time, one for part-time), and no need for a custom constructor.

public enum Classifation { FULL_TIME, PART_TIME }

I would nest the type-of-worker enum within the worker class. That nesting makes clear that the type-of-worker enum objects are mainly used only within that class.

public record Worker( int id , String name , Classifation classifation )
{
    public enum Classifation { FULL_TIME, PART_TIME }
}

Usage:

Worker adam = new Worker( 1 , "Adam" , Worker.Classifation.FULL_TIME );
Worker milne = new Worker( 2 , "Milne" , Worker.Classifation.PART_TIME );

System.out.println( List.of( adam , milne ) );

When run.

[Worker[id=1, name=Adam, classifation=FULL_TIME], Worker[id=2, name=Milne, classifation=PART_TIME]]

Your title asks:

Reference Table OR Java Enum which is better

There are at least two factors to consider:

Will the infrequent changes happen only while you are programming your Java app?
Will other apps or the DBA need to do lookups on this data?

An enum in Java is for automatically instantiating a limited set of named objects known at compile-time. You cannot change the enum at runtime.

If there is any possibility of this information changing while your app is deployed, then an enum is not appropriate. Instead, you need to dynamically create a collection of objects during runtime.

If there is any possibility of other apps, or the DBA, needing to look up this information, then you should record that info within the database rather than within your Java app.

Given your example scenario, I suspect you may want both enums in Java and look-up reference table in database.

Another approach is to use human-readable text instead of a plain integer as a WORKER_TYPE column. In your case, store textual values such as Full-time & Part-time, or Full & Part. In this case, no need for the loop-up reference table. This approach assumes (A) there is no business need for the type code (1 or 2), and (B) there are no further columns to track in describing a type of worker.

Furthermore, in more sophisticated databases, you can have the database enforce the limited set of possible values being stored in that column.

Reference Table OR Java Enum which is better

1 Answers