What's the best way to store a value from a list of pre-defined values in a database?

Question

Let's say I have a pre-defined list of values (RW, FW, 4W) representing drive type of a vehicle:

RW - Rear Wheel

FW - Front Wheet

4W - Four Wheel

Now, I want to take a value from the above 3 values as an input from my user and then store it in a database.

Upto my knowledge, I can perform this with the help of any of the following methods:

- Hard-code the values at the UI so that the UI shows a drop-down having only the above 3 values. Then store that value in the String vehicleType field of the Vehicle vehicle object and then store it in the DB as String.

• Cons:

  i). No validation of the value at object level

  ii). No validation of the value at DB level.

  iii). Though the need for adding a new value to the list is rare, but still user can't add a new value at runtime

  - Pros:

  i). No need of join at DB to retrieve the vehicle object

OR

• Make a separate table VEHICLE_TYPE in the DB having all the 3 values and link it with the VEHICLE table via. foreign key. And then populate the drop-down at UI from the VEHICLE_TYPE table. Store the value in the vehicle object as String

  - Cons:

  i). No validation at object level

  ii). Need a join at DB to retrieve a vehicle object

  - Pros:

  i). validation of the value at DB level (by foreign key)

  ii). User can add a new value to the list at runtime

OR

• Make a separate table VEHICLE_TYPE in the DB having all the 3 values but DON'T link it with the VEHICLE table via. foreign key. And then populate the drop-down at UI from the VEHICLE_TYPE table. Store the value in the vehicle object and in the DB as String

  - Cons:

  i). No validation at object level

  ii). No validation at DB level

  - Pros:

  i). No join required at DB level

  ii). User can add new value to the list

OR

• Make a separate table VEHICLE_TYPE in the DB having all the 3 values and link it with the VEHICLE table via. foreign key. And then populate the drop-down at UI from the VEHICLE_TYPE table. Make an enum VehicleType in java and then add a field VehicleType vehicleType in the Vehicle class. Store a value from the VehicleType enum in the vehicleType field based on the input of the user.

  -Cons:

  i). Will have to update the list at two places: VehicleType enum and the VEHICLE_TYPE table. May cause inconsistency.

  ii). User can't add a new value to the list (he can add a value in the table but can't change the enum)

  - Pros:

  i). validation at UI level

  ii). validation at object level

  iii). validation at DB level

Question: Is there other way by which we can perform the above task which doesn't have any of the above disadvantages?

Answer 1

Sure. Your second one with a modification:

Make a separate table VEHICLE_TYPE in the DB having all the 3 values and link it with the VEHICLE table via. foreign key. And then populate the drop-down at UI from the VEHICLE_TYPE table. Store the value in the vehicle object as String. When calling vehicle.setVehicleType(), verify that the value assigned is valid by checking the possible values from the DB. If it's invalid, throw an InvalidArgumentException or a subclass.

Now you have validation in the object. And also, I don't consider having to do a join a con. You can't do much of anything without joining tables. That's why you have many tables.