假设我有一个代表车辆驱动类型的预定义值列表（RW、FW、4W）：
RW - 后轮
FW > - 前轮
4W - 四轮
现在，我想从上述 3 个值中取出一个值作为用户的输入，然后将其存储在数据库中。
据我所知，我可以借助以下任何方法来执行此操作：
- 对 UI 上的值进行硬编码，以便 UI 显示仅包含上述内容的下拉列表3 个值。然后将该值存储在 Vehiclevehicle 对象的 StringvehicleType 字段中，然后将其作为 String 存储在数据库中。
或
或
或
-
在数据库中创建一个单独的表VEHICLE_TYPE，其中包含所有3个值，并且通过将其与 VEHICLE 表链接。外键。然后从 VEHICLE_TYPE 表填充 UI 的下拉列表。在java中创建一个枚举VehicleType，然后在Vehicle类中添加一个VehicleTypevehicleType字段。根据用户的输入，将 VehicleType 枚举中的值存储在 vehicleType 字段中。
-缺点：
我）。必须在两个位置更新列表：VehicleType 枚举和VEHICLE_TYPE 表。可能会导致不一致。
ii).用户无法向列表添加新值（他可以在表中添加值，但无法更改枚举）
- 优点：
我）。 UI 级别的验证
ii).对象级别的验证
三）。数据库级别的验证
问题：
我们是否有其他方法可以执行上述任务并且没有上述任何缺点？
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?
发布评论
评论（4）
当然。第二个修改：
在数据库中创建一个单独的表
VEHICLE_TYPE，其中包含所有3个值，并将其与VEHICLE表通过链接。外键。然后从VEHICLE_TYPE表填充 UI 的下拉列表。将值存储在车辆对象中作为String。调用vehicle.setVehicleType()时，通过检查数据库中的可能值来验证分配的值是否有效。如果无效，则抛出InvalidArgumentException或子类。现在您已经在对象中进行了验证。而且，我不认为必须参加一个骗局。如果不连接表，您将无法做很多事情。这就是为什么你有很多桌子。
Sure. Your second one with a modification:
Make a separate table
VEHICLE_TYPEin the DB having all the 3 values and link it with theVEHICLEtable via. foreign key. And then populate the drop-down at UI from theVEHICLE_TYPEtable. Store the value in the vehicle object asString. When callingvehicle.setVehicleType(), verify that the value assigned is valid by checking the possible values from the DB. If it's invalid, throw anInvalidArgumentExceptionor 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.
我也会选择第二种方法。关于第一个骗局，您已经得到了答复。
关于第二个缺点，如果性能如此重要，您可以使用以下方法之一：
如果类型车辆在应用程序中使用不多，则延迟加载车辆类型。
如果您没有使用数据库 ID，因为您使用车辆类型的代码作为主键，您可以向车辆类添加 codeType 属性，并加载此属性而不是类型（也可以加载懒惰地，根据需要），直接从车辆表。那么你就不会有任何连接。
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:
If the type vehicle is not used much in the application, lazy loading the type of the vehicle.
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.
创建一个单独的表 Vehicle_type (Vehicle_type_id int, description varchar (您需要确定适当的大小)) 用作下拉菜单的查找。如果您希望在查找更改时主表中的值发生更改（例如 adimin 将 seden 更改为 sedan），则将 typeid 存储在车辆表中。如果您希望这是历史数据（也许不再有轿车类型，但较旧的车辆仍应标记为轿车），则将类型的描述存储在车辆表中。在第二种情况下，您无法强制执行 FK 关系，因此您需要确保插入（以及仅更新该值）无法选择当前不在表中的值。应用程序可能会执行此操作，但如果值可能在应用程序外部发生更改，您可以编写一个触发器来执行此操作。
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.
我不认为联接应该成为您担心的原因 - 您很可能会发现通过妥协设计来减少联接的开销很可能是浪费精力。您到数据库的网络延迟可能高于 JOIN 开销。
如何处理用户输入的附加值取决于您希望如何处理它们：
将它们视为真正的附加值。它们被添加到数据库中的 VEHICLE_TYPE 中，添加后可供所有用户选择。
将它们视为该特定字段的自定义值。即，VEHICLE_TYPE 包括类型“其他”并且用户可以在单独的字段中输入附加详细信息。这些不会与其他用户共享，也不会出现在下拉列表中。
要获得对象级验证，请根据 VEHICLE_TYPE 进行验证。这可以通过现代 OIM 和 ORM 框架自动完成。这些允许您在模型上定义验证规则，然后将其向前传播到 UI 以尽早捕获验证错误，并向后传播到数据库以确保数据存储的一致性。
您可以将车辆 ID 存储为常规键，或类型字符串本身（RW、FW 等）。如果使用类型字符串本身，则不必加入 VEHICLE_TYPE 表。您可以直接呈现字符串，或者如果需要本地化，也可以从资源包中获取呈现字符串。
编辑：要了解 ORM 和 OIM 如何将模型验证元数据带回数据库并输出到 UI，请参阅 DZone：Hibernate 4 验证，以及 Metawidget。使用 JSR 303，您可以验证 UI、业务层和后端中的对象。
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:
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.
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.