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

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.

Answer 2

I would chose also the second approach. You have been already answered about the first con.

Regarding the second con, if performance is so important, you could use one of those approaches:

1. If the type vehicle is not used much in the application, lazy loading the type of the vehicle.

2. If you are not using database ids, because you are using the code of the type vehicle as primary key, you could add a codeType property to your vehicle class, and load this property instead of the type (which could also be loaded lazyly, depending on needs), directly from the vehicle table. Then you won't have any join.

Answer 3

I don't feel that joins should be your cause for concern - you might well find that compromising the design to reduce the overhead of a JOIN is most likely going to be wasted effort. Your network latency to the db could be higher than the JOIN overhead.

How you deal with additional values entered by the user depends upon how you want them to be handled:

1. Treat them as true additional values. They are added to the VEHICLE_TYPE in the database, and once added, are available for all users to select.

2. Treat them as custom values for that particular field. I.e. the VEHICLE_TYPE includes a type "Other" and the user can enter additional details in a separate field. These are not shared with other users and do not appear in the dropdown list.

To get object-level validation, validate against the VEHICLE_TYPE. This can be done automatically with modern OIM and ORM frameworks. These allow you to define validation rules on the model which are then propagated forward to the UI for early catching of validation errors, and backwards to the database to ensure data store consistency.

You can store Vehicle ID as regular key, or the type string itself (RW,FW etc.). If using the type string itself, you don't have to join to the VEHICLE_TYPE table. You could present the string directly, or you can fetch the presentation strings from resource bundles if localization is needed.

EDIT: To see how ORM and OIM can take model validation metadata back to the db and out to the UI, see DZone: Hibernate 4 Validation, and Metawidget. With JSR 303 you can validate your objects in the UI, business layer and back end.

Answer 4

Make a separate table Vehicle_type (Vehicle_type_id int, description varchar (you need to determine the appropraite size))to use as the lookup for the drop down menu. If you want the value to change in the main table when the look up changes (say an adimin changes seden to sedan), then store the typeid in the vehicle table. If you want this to be historical data (maybe there is no longer a type sedan but older vehicles should still be marked as sedan) then store the decription of the type in the vehicle table. In the second case you can't enforce with an FK relationship, so you will need to ensure that inserts (and updates of that value only) cannot choose values not currently in the table. The application will likely do this although you could write a trigger to do so if values are likely to change outside the application.