带外键的数据库设计问题
我正在尝试正确使用外键来维护数据完整性。我并不是一个真正的数据库专家，所以我想知道是否有一些我不知道的一般设计原则。这是我正在尝试做的一个示例：
假设您想要构建一个包含类型（汽车、卡车等）、品牌和型号的车辆数据库。用户必须至少输入类型，但品牌和型号是可选的（如果给出型号，则需要品牌）。我的第一个想法是这样设置数据库：
Type:
-id (PK)
-description
Make:
-id (PK)
-type_id (FK references Type:id)
-description
Model:
-id (PK)
-make_id (FK references Make:id)
-description
Vechicle:
-id (PK)
-type_id (FK references Type:id)
-make_id (FK references Make:id)
-model_id (FK references Model:id)
如何设置车辆的 FK 以确保类型、品牌和型号全部匹配？例如，如何防止车辆具有（类型：摩托车，品牌：福特，型号：思域）？其中每一个都是有效的 FK，但它们不维护通过其他表的 FK 显示的关系。
另外，因为 Model 不是必需的，所以我不能只存储 model_id FK 并从中逆向工作。
我完全不受数据库设计的束缚，因此我愿意改变表的设置方式。有什么想法吗？
PS - 如果有人感兴趣的话，我正在使用 mysql，但这更多是关于数据库的一般问题。
编辑（澄清）：
车辆表中需要-type_id和make_id，除非有某种方法可以在model_id为空的情况下找出它们；
-需要维护type_id、make_id和model_id之间的关系。
I'm trying to use foreign keys properly to maintain data integrity. I'm not really a database guy so I'm wondering if there is some general design principle I don't know about. Here's an example of what I'm trying to do:
Say you want to build a database of vehicles with Type (car, truck, etc.), Make, and Model. A user has to input at least the Type, but the Make and Model are optional (if Model is given, then Make is required). My first idea is to set up the database as such:
Type:
-id (PK)
-description
Make:
-id (PK)
-type_id (FK references Type:id)
-description
Model:
-id (PK)
-make_id (FK references Make:id)
-description
Vechicle:
-id (PK)
-type_id (FK references Type:id)
-make_id (FK references Make:id)
-model_id (FK references Model:id)
How would you setup the FKs for Vehicle to ensure that the Type, Make, and Model all match up? For example, how would you prevent a vehicle having (Type:Motorcyle, Make:Ford, Model:Civic)? Each of those would be valid FKs, but they don't maintain the relationships shown through the other tables' FKs.
Also, because Model isn't required, I can't just store the model_id FK and work backwards from it.
I'm not tied to the database design at all, so I'm open to the possibility of having to change the way the tables are set up. Any ideas?
P.S. - I'm using mysql if anyone's interested, but this is more of a general question about databases.
Edit (Clarifications):
-type_id and make_id are needed in the vehicle table unless there is some way to figure those out in the case that model_id is null;
-the relationships between type_id, make_id, and model_id need to be maintained.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
您正在寻找的是 CHECK 约束。不幸的是 MySQL 目前不支持这一点。您可以使用触发器模拟此类功能，但需要创建 INSERT 和 UPDATE 触发器才能使其工作。
然而，正如其他答案所表明的那样，您真正应该存储的只是车辆型号。在您的应用程序中，您应该深入了解该类型（如果可用）。
What you are looking for is a CHECK constraint. Unfortunately MySQL does not currently support this. You could emulate such functionality with triggers but you would need to create both an INSERT and an UPDATE trigger for it to work.
However, as other answers have indicated, all you should really be storing is the vehicle model. In you application you should be drilling down to the type if it's available.
像这样：
类型：
品牌：
型号：
车辆：
基本上也不要重复引用车辆的品牌和类型。如果你这样做，你就会遇到问题。您可以从车辆型号中获取品牌和类型（如果已定义）。型号必须有品牌。 Make必须有类型。
想一想：如果车辆有给定的型号，但车辆和型号都有品牌，那么这些值可能会不同。这种不一致可能是由于信息冗余而产生的。一般来说，您希望避免这种情况。
如果您需要找出车辆的品牌和类型，SQL 开始如下所示：
等等。
Like this:
Type:
Make:
Model:
Vechicle:
Basically don't double reference make and type from vehicle as well. You'll run into problems if you do that. You can get the make and type from the model of the vehicle (if defined). Model must have make. Make must have type.
Think about that for a second: if vehicle has a given model but vehicle and model both have a make, those values can be different. This kind of inconsistency can develop because of information redundancy. You want to avoid that generally.
If you need to figure out the make and type of a vehicle the SQL starts to look like this:
And so on.
这是一种方法：
- 一个品牌（福特、通用汽车、本田）可以有多个型号，一个型号只属于一个品牌。
- 模型属于某种类型（汽车、卡车自行车）。
- 车辆具有特定型号。一辆车辆只能是一种型号；一个型号可以有很多车辆。
模型表包含所有模型共有的列；而汽车、卡车和摩托车则各有特定的列。
在对数据库进行建模时，请考虑数据、实体和关系；不要从 UI 开始——中间有一个业务层来解决问题。使用MySQL就可以了，你可以在你的应用层强制检查和外键约束。
Here is one approach:
- One make (Ford, GM, Honda) can have many models, one model belongs to only one make.
- Model is of a certain type (car, truck bike).
- Vehicle is of a certain model. One vehicle can be of only one model; there can be many vehicles of a model.
Model table contains columns common to all models; while car, truck, and motorcycle have columns specific to each one.
When modeling a DB, consider data, entities and relationships; don't start from the UI -- there is a business layer in between to sort things out. It is OK to use MySQL, you can enforce check and foreign key constraints on your application layer.
您的设计对于数据完整性来说是很好的，您的应用程序将负责维护车辆必须由特定类型的品牌和特定品牌的型号组成。
如果您想维护数据库中车辆类型/品牌/型号的完整性，您可以向车辆表添加检查约束，以确保车辆品牌的类型 ID 等于提供的类型 ID。如果模型 id 不为空，请确保它的 make id 与提供的 make id 相同。
Your design is fine for data integrity, it will be the job of your application to maintain that a Vehicle must be made up of Makes from a particular Type and Models of a particular Make.
If you want to maintain vehicle type/make/model integrity in the database you could add a check constraint to your Vehicle table that makes sure the Vehicle's make's type id equals the provided type id. And if the model id is not null, make sure it's make id is the same as the make id provided.
我看到您已经接受了答案，但是处理您的实际结构问题并且不使用触发器或检查约束的另一种方法是在品牌和型号表中创建虚拟条目，并描述“n/a”或类似的内容，分别对应“类型”和“制造商”中的每个条目，然后删除“车辆”中的冗余列。
这样，如果您只知道车辆的类型，您将在 Make 中找到引用适当类型的虚拟条目，然后在 Model 中找到引用该 Make 的虚拟条目，然后从新行中引用该 Model车辆。
当然，主要的缺点是需要额外的内务处理来创建虚拟行，无论是在添加类型或品牌时提前，还是在添加丢失数据的车辆时按需创建。
I see you already accepted an answer, but an alternate approach that handles your actual structural problem and doesn't use triggers or check constraints would be to create dummy entries in the Make and Model tables with a description of "n/a" or such, one for each entry in Type and Make respectively, and then get rid of the redundant columns in Vehicle.
That way, if all you know is the Type of a vehicle, you'd find the dummy entry in Make that references the appropriate Type, then find the dummy entry in Model that references that Make, then reference that Model from the new row in Vehicle.
The main downsides of course would be extra housekeeping to create the dummy rows, either ahead of time when adding a Type or Make, or on demand when adding a Vehicle with missing data.