在多对多关系中强制公共外键 [英] 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 many locations and a restaurant can have many offers.

这些关系很容易表示：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 certain locations of a restaurant.

这个限制的建模似乎很容易在开头：只是做一个关联表与两个外键，一个从 表中，另一个来自 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屋！