在多对多关系中强制公共外键 [英] Enforcing common foreign key in many-to-many relationship
问题描述
我有以下有趣的数据建模问题。我将使用餐馆的例子来说明它:考虑以下三个实体:餐厅
,位置
和优惠
。
I have the following interesting data modeling problem. I am going to use the example of restaurants to illustrate it: Consider the following three entities: Restaurant
, Location
and Offer
.
-
餐厅可以有多个
位置
和餐厅
可以有许多优惠
。
- A
restaurant
can have manylocations
and arestaurant
can have manyoffers
.
这些关系很容易表示:A Restaurant
table;从餐厅
表中选择位置
表与 FK
和餐厅
表中的提供
表和 FK
Those relationships are easy to represent: A Restaurant
table; a Location
table with a FK
from the Restaurant
table; and a Offer
table with a FK
from the Restaurant
table.
现在有趣的问题是:
-
餐厅的
位置
有效。
- An
offer
can only be valid on certainlocations
of arestaurant
.
这个限制的建模似乎很容易在开头:只是做一个关联表与两个外键,一个从
表中,另一个来自 Location
表。
The modeling of that restriction seems easy at the beginning: just do an association table with two foreign keys, one from the Offer
table, and another one from the Location
table.
该解决方案的问题是它不会限制我关联提供
和位置
不属于同一家餐厅。
The problem with that solution is that it does not restrict me from associating offers
and locations
that do not belong to the same restaurant.
我如何以更好的方式对这个模型进行建模,从而允许我在数据库级别实施这个限制?
How could I model this in a better way that would allow me to enforce that restriction at the database level?
推荐答案
我的假设是,在你目前的模型中有1家餐厅,而Offer有1家餐厅。
My assumption is that in your current model Location has 1 restaurant and Offer has 1 Restaurant.
通过在Offer:(Restaurant_ID,Offer_ID)上创建一个复合键,并使用此键作为外键从Location_Offers到Offer。
You can solve your problem by making a compound key on Offer: (Restaurant_ID, Offer_ID) and use this key as a foreign key from Location_Offers to Offer.
您可以在位置:make复合键(Restaurant_ID,Location_ID),并将其用作Locations_Offer到位置的外键。
You can do the same on Location: make a compound key (Restaurant_ID, Location_ID) and use this as a foreign key from Locations_Offer to Location.
这可确保Locations_Offer中链接位置和要约的任何记录,仅链接与同一餐厅有关系的人。
This ensures that any record in Locations_Offer that links a Location and an Offer, only links those that have a relation with the same Restaurant.
这篇关于在多对多关系中强制公共外键的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!