What is the best way to store a value from a list of predefined values ​​in a database?

Question

Say I have a predefined list of values ​​( RW, FW, 4W ) representing the type of vehicle drive:

RW - Rear Wheel

FW - Front Wheet

4W - Four Wheels

Now I want to accept a value from the above three values ​​as input from my user, and then save it to the database.

Complementing my knowledge, I can accomplish this using any of the following methods:

- Hard code the values ​​in the user interface so that the user interface displays a drop-down list that has only the values ​​indicated above. Then, save this value in the String vehicleType field of the Vehicle vehicle , and then save it in DB as a String .

• Against:

  I). No object level value validation

  II). Lack of DB value verification.

  III). Although the need to add a new value to the list is rare, but still the user cannot add a new value at run time

  - Pros:

  I). No need to join in DB to retrieve vehicle object

OR

• Make a separate table VEHICLE_TYPE in a database with all 3 values, and link it to the vehicle table through. external key. And then fill in the drop-down menu in the VEHICLE_TYPE table. Save the value in the vehicle object as a String

  - Minuses:

  I). No object level validation

  II). You need to join in the database to get the vehicle object

  - Pros:

  I). checking the value at the 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 database that has all 3 values, but DO NOT link it to the vehicle table through. external key. And then fill in the drop-down menu in the VEHICLE_TYPE table. Store the value in vehicle and in DB as String

  - Minuses:

  I). No object level validation

  II). No DB level validation

  - Pros:

  I). No join required at DB level

  II). User can add a new value to the list.

OR

• Make a separate table VEHICLE_TYPE in a database with all 3 values, and link it to the vehicle table through. external key. And then fill in the drop-down menu in the VEHICLE_TYPE table. Create an enum VehicleType in java, and then add the VehicleType vehicleType field to the vehicle class. Save the value from the VehicleType enumeration in the VehicleType field based on user input.

  -Cons:

  I). You will need to update the list in two places: VehicleType enumeration and table VEHICLE_TYPE . May cause inconsistency.

  II). The user cannot add a new value to the list (he can add a value to the table, but cannot change the enumeration)

  - Pros:

  I). user interface check

  II). object level verification

  III). DB check

Question: Is there any other way by which we can perform the aforementioned task that does not have any of the disadvantages listed above?

Answer 1

Of course. The second option with the modification:

Make a separate table VEHICLE_TYPE in the database, which has all 3 values, and connect it to the VEHICLE table through. external key. And then fill in the drop-down menu in the VEHICLE_TYPE table. Save the value in the car object as a String . When calling vehicle.setVehicleType() make sure that the assigned value is valid by checking the possible values ​​from the database. If it is not valid, InvalidArgumentException or a subclass.

Now you have a check in the object. And also, I do not think that we need to unite. You cannot do anything without joining the tables. This is why you have so many tables.

Answer 2

I would choose the second approach. You have already answered the first question.

As for the second con, if performance is so important, you can use one of these approaches:

• If the type of vehicle is little used in the application, lazy loading is the type of vehicle.

• If you do not use database identifiers because you use the vehicle type code as the primary key, you can add the codeType property to your vehicle class and load this property instead of the type (which could also be loaded lazily, depending on needs) directly from the vehicle table. Then you will have no connection.

Answer 3

I don't think associations should be a cause for concern - you may well find that compromising a design to reduce JOIN overhead is likely to be wasted. The latency of your network in db may be higher than the JOIN overhead.

How you deal with additional values ​​entered by the user depends on how you want to handle them:

• Treat them as true complementary values. They are added to VEHICLE_TYPE in the database and after they are added are available to all users.

• Treat them as custom values ​​for this particular field. That is, VEHICLE_TYPE includes the type "Other", and the user can enter additional data in a separate field. They are not shared with other users and are not displayed in the drop-down list.

To get object level validation, check it at VEHICLE_TYPE. This can be done automatically using modern OIM and ORM structures. This allows you to define model validation rules, which then propagate forward to the user interface for early detection of validation errors and back to the database to ensure data warehouse consistency.

You can save the vehicle identifier as a regular key or the string itself (RW, FW, etc.). If you use a type string, you do not need to join the VEHICLE_TYPE table. You can present a string directly or you can get presentation strings from resource packages if localization is required.

EDIT: to see how ORM and OIM can return model validation metadata back to db and exit the user interface, see DZone: Hibernate 4 Validation and Metawidget . With JSR 303, you can test your objects in the user interface, business layer, and at the end.

Answer 4

Create a separate Vehicle_type table (Vehicle_type_id int, varchar description (you need to determine the size of the appropriate size)) to use it to search in the drop-down menu. If you want the value to change in the main table when the view changes (for example, adimin changes seden to sedan), save the typeid in the car table. If you want this to be historical data (maybe there is no longer a sedan type, but old cars should still be marked as a sedan), then save the decription of this type in the car table. In the second case, you cannot apply the FK relationship, so you will need to make sure that the inserts (and updates of only this value) cannot select values ​​that are not currently listed in the table. An application is likely to do this, although you can write a trigger to do this if the values ​​can change outside the application.