关系数据库建模的正确方法
为了解释我的问题，我将举一个简单的例子：
我的数据库有三个表：
[positions] - position_id INT - position VARCHAR [employees] - employee_id INT - position_id INT - FK - name VARCHAR - birth_date DATE [vehicles] - vehicle_id INT - model VARCHAR - year VARCHAR - color VARCHAR
问题是我必须将一辆车与公司中职位为“司机”的一名员工关联起来，并且仅在这种情况下。
我尝试使用继承并创建另一个名为“Driver”的表，该表具有与一名员工关联的外键（1-1 关系），但我无法使其工作，因为在编程阶段我必须手动验证是否选择了位置 id（在 HTML select 元素中）是“Driver”的 id。我认为这不是一个好的编程实践。
总之，我想知道是否有其他方法可以在不影响关系数据库或编程的情况下做到这一点。
提前致谢！ 抱歉英语不好，这不是我的主要语言。 我希望你能理解。
to explain my problem, I'll give a simple example:
My database has three tables:
[positions] - position_id INT - position VARCHAR [employees] - employee_id INT - position_id INT - FK - name VARCHAR - birth_date DATE [vehicles] - vehicle_id INT - model VARCHAR - year VARCHAR - color VARCHAR
The problem is that I must associate one vehicle with one employee whose position in the company is "Driver", and only in that case.
I tried to use inheritance and create another table called "Driver" having a ForeignKey associated with one employee (1-1 relationship), but I couldn't make it work because in the programming stage I'll have to manually verify if the selected position id (in the HTML select element) is the id of the "Driver". I believe that is not a good programming practice.
In conclusion, I would like to know if there are other ways to do this without prejudice the relational database or the programming.
Thanks in advance!
And sorry for the bad english, it's not my primary language.
I hope you can understand.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
这是一条业务规则：“只有职位 = 驾驶的员工才能与车辆关联”。业务规则通常在编程中实现，这并不是一个坏习惯。编程是为了编写业务逻辑而设计的。一般来说，在开发任何应用程序时，您将获得大量无法在数据库级别实现的此类实例。
但是，如果您仍然想在数据库级别控制它，那么您可以使用触发器并在插入/更新级别检查此验证。
This is a business rule that - "Only employee with position = drive can be associated with a vehicle". Business rules are generally implemented in the programming and its not a bad practice. Programming is made for writing business logic. In general you will get tons of such instance which cannot be implemented at database level while developing any application.
However if you still want to control this at DB level then you can use trigger and check this validation at insert/update level.
不幸的是，关系数据库无法构建良好的分层对象存储。您可能会考虑使用某种对象关系模型来伪造它，但您是对的：这不是一个好的做法。也许可以考虑使用专门构建的对象数据存储而不是传统的 RDBMS。
Unfortunately relational databases just don't make good hierarchical object stores. You might consider using some kind of Object-Relation Model to fake it, but you're right: it's not good practise. Perhaps consider a purpose-built object datastore instead of a traditional RDBMS.
有多种方法可以实现这一点，但需要进行各种权衡。 Scott Ambler 有一个很棒的页面，用图表列出了替代方案。
There are a number of ways to do it with various tradeoffs. Scott Ambler has a great page listing the alternatives with diagrams.
实现此目的的最佳方法可能是使用一个将员工与车辆连接起来的表
EmployeeVehicles。是的，这意味着您的应用程序（或者可能是触发器或存储过程）必须确保只有特定类型的Employee才会在EmployeeVehicles中实际拥有记录，但是那些通常是存储业务逻辑的最佳位置。数据库的作用是尽可能以最标准化的方式存储数据，而不是跟踪特定于业务的规则。据其需要了解，某些员工 (0..*) 可能拥有车辆（1..*，或者可能是 1..1）。The best way to do this is likely to have a table
EmployeeVehiclesthat connects employees to vehicles. Yes, this means your application (or perhaps a trigger or stored procedure) would have to ensure that only the specific types ofEmployeewould actually have a record inEmployeeVehicles, but those are typically the best places to store your business logic. The database is there to store data in the most normalized way possible, not to keep track of business-specific rules. As far as it needs to know, some employees (0..*) might have vehicles (1..*, or maybe 1..1).