Database enum fields

Question

I'm trying to simulate a Java Enum type in a Microsoft SQL Server table:

CREATE TABLE ResultType(
    result TINYINT PRIMARY KEY,
    description VARCHAR(30)
);

INSERT INTO ResultType
VALUES (0, 'Pass with distinction'),
       (1, 'Pass with credit'),
       (2, 'Pass'),
       (3, 'Fail first attempt),
       (4, 'Fail and debar');

CREATE TABLE CandidateResult(
    candidate_id INT PRIMARY KEY,
    candidate_name NVARCHAR(100),
    result TINYINT References ResultType(result)
)

Now, what is the best practice for ensuring that the ResultType table is consistent with a Java ResultType enum?

enum ResultType { DISTINCTION, CREDIT, PASS, FAIL, DEBAR };
...
ResultType result;
if (mark > 90) {
    result = ResultType.DISTINCTION;
} else if (mark > 70)
    result = ResultType.CREDIT;
}
etc...
preparedStatement.setInt(1, result.ordinal)

The above doesn't look robust enough to me: I have to manually and constantly ensure that the enum agrees with the values in the ResultType table. How should I ensure this correspondence always holds, but without creating a performance overhead?

Alexander Kashpirovsky · Answer

I'd good way, based on dictionary table ResultType. And you can build SQL reports with human readable name of types. Also using of table ResultType can provides same additional information, 'outdated' type for example.

select cr.*, ct.description 
from CandidateResult cr 
join ResultType ct 
on cr.result = ct.result 
;

Database enum fields

1 Answers