这不是世界上最优雅的解决方案，但是您可以使用具体的表继承来实现这一点。

Conceptually you are proposing a notion of a class of "things that can be popular areas" from which your three types of places inherit. You could represent this as a table called, for example, places where each row has a one-to-one relationship with a row in regions, countries, or states. (Attributes that are shared between regions, countries, or states, if any, could be pushed into this places table.) Your popular_place_id would then be a foreign key reference to a row in the places table which would then lead you to a region, country, or state.

您在第二篇专栏文章中提出的描述关联类型的解决方案恰好是Rails如何处理多态关联，但我并不喜欢这种方法。Bill非常详细地解释了为什么多态关联不是你的朋友。

你所描述的被称为多态关联。也就是说，“外键”列包含一个必须存在于一组目标表中的id值。通常，目标表以某种方式相互关联，例如是一些公共数据超类的实例。在外键列旁边还需要另一列，以便在每一行上指定引用哪个目标表。

CREATE TABLE popular_places (
  user_id INT NOT NULL,
  place_id INT NOT NULL,
  place_type VARCHAR(10) -- either 'states' or 'countries'
  -- foreign key is not possible
);

没有办法使用SQL约束来建模多态关联。外键约束总是引用一个目标表。

Rails和Hibernate等框架支持多态关联。但是它们明确指出，必须禁用SQL约束才能使用该特性。相反，应用程序或框架必须做相同的工作，以确保引用得到满足。也就是说，外键中的值出现在一个可能的目标表中。

多态关联在加强数据库一致性方面很弱。数据完整性依赖于所有客户端使用相同的引用完整性逻辑访问数据库，而且执行必须是无错误的。

以下是一些利用数据库强制引用完整性的替代解决方案:

为每个目标创建一个额外的表。例如，popular_states和popular_nations分别引用州和国家。这些“流行”表中的每一个都引用了用户的个人资料。

CREATE TABLE popular_states (
  state_id INT NOT NULL,
  user_id  INT NOT NULL,
  PRIMARY KEY(state_id, user_id),
  FOREIGN KEY (state_id) REFERENCES states(state_id),
  FOREIGN KEY (user_id) REFERENCES users(user_id),
);

CREATE TABLE popular_countries (
  country_id INT NOT NULL,
  user_id    INT NOT NULL,
  PRIMARY KEY(country_id, user_id),
  FOREIGN KEY (country_id) REFERENCES countries(country_id),
  FOREIGN KEY (user_id) REFERENCES users(user_id),
);

这意味着要获得用户最喜欢的所有地方，您需要查询这两个表。但这意味着您可以依赖数据库来执行一致性。

创建一个places表作为超表。正如Abie提到的，第二种选择是您的流行地点引用一个像places这样的表，它是州和国家的父表。也就是说，州和国家都有一个地方的外键(您甚至可以使这个外键也成为州和国家的主键)。

CREATE TABLE popular_areas (
  user_id INT NOT NULL,
  place_id INT NOT NULL,
  PRIMARY KEY (user_id, place_id),
  FOREIGN KEY (place_id) REFERENCES places(place_id)
);

CREATE TABLE states (
  state_id INT NOT NULL PRIMARY KEY,
  FOREIGN KEY (state_id) REFERENCES places(place_id)
);

CREATE TABLE countries (
  country_id INT NOT NULL PRIMARY KEY,
  FOREIGN KEY (country_id) REFERENCES places(place_id)
);

使用两列。使用两个列，而不是一个列可以引用两个目标表中的任何一个。这两列可以是NULL;事实上，它们中只有一个应该是非null。

CREATE TABLE popular_areas (
  place_id SERIAL PRIMARY KEY,
  user_id INT NOT NULL,
  state_id INT,
  country_id INT,
  CONSTRAINT UNIQUE (user_id, state_id, country_id), -- UNIQUE permits NULLs
  CONSTRAINT CHECK (state_id IS NOT NULL OR country_id IS NOT NULL),
  FOREIGN KEY (state_id) REFERENCES places(place_id),
  FOREIGN KEY (country_id) REFERENCES places(place_id)
);

就关系理论而言，多态关联违反了第一范式，因为popul_place_id实际上是一个具有两个含义的列:它要么是一个州，要么是一个国家。您不会将一个人的年龄和电话号码存储在一个列中，出于同样的原因，您也不应该将state_id和country_id存储在一个列中。这两个属性具有兼容的数据类型是巧合;它们仍然表示不同的逻辑实体。

多态关联也违反了第三范式，因为列的含义依赖于外键所指向的表的额外列。在第三范式中，表中的属性必须仅依赖于该表的主键。

关于@SavasVedova的评论:

在没有看到表定义或示例查询的情况下，我不确定是否符合您的描述，但听起来好像您只是有多个Filters表，每个表包含一个引用中央Products表的外键。

CREATE TABLE Products (
  product_id INT PRIMARY KEY
);

CREATE TABLE FiltersType1 (
  filter_id INT PRIMARY KEY,
  product_id INT NOT NULL,
  FOREIGN KEY (product_id) REFERENCES Products(product_id)
);

CREATE TABLE FiltersType2 (
  filter_id INT  PRIMARY KEY,
  product_id INT NOT NULL,
  FOREIGN KEY (product_id) REFERENCES Products(product_id)
);

...and other filter tables...

如果你知道你想要加入哪种类型的过滤器，将产品连接到特定类型的过滤器是很容易的:

SELECT * FROM Products
INNER JOIN FiltersType2 USING (product_id)

如果希望筛选器类型是动态的，则必须编写应用程序代码来构造SQL查询。SQL要求在编写查询时指定并固定表。不能根据Products的各个行中找到的值动态地选择联接表。

唯一的其他选项是使用外部连接连接到所有筛选表。那些没有匹配product_id的函数将作为一行null返回。但是您仍然必须硬编码所有连接的表，如果您添加新的筛选表，您必须更新您的代码。

SELECT * FROM Products
LEFT OUTER JOIN FiltersType1 USING (product_id)
LEFT OUTER JOIN FiltersType2 USING (product_id)
LEFT OUTER JOIN FiltersType3 USING (product_id)
...

连接到所有过滤表的另一种方法是连续执行:

SELECT * FROM Product
INNER JOIN FiltersType1 USING (product_id)
UNION ALL
SELECT * FROM Products
INNER JOIN FiltersType2 USING (product_id)
UNION ALL
SELECT * FROM Products
INNER JOIN FiltersType3 USING (product_id)
...

但是这种格式仍然要求您编写对所有表的引用。这是无法回避的。

以下是对Bill Karwin的“超表”方法的更正，使用复合键(place_type, place_id)来解决感知到的规范形式违规:

CREATE TABLE places (
  place_id INT NOT NULL UNIQUE,
  place_type VARCHAR(10) NOT NULL
     CHECK ( place_type = 'state', 'country' ),
  UNIQUE ( place_type, place_id )
);

CREATE TABLE states (
  place_id INT NOT NULL UNIQUE,
  place_type VARCHAR(10) DEFAULT 'state' NOT NULL
     CHECK ( place_type = 'state' ),
  FOREIGN KEY ( place_type, place_id ) 
     REFERENCES places ( place_type, place_id )
  -- attributes specific to states go here
);

CREATE TABLE countries (
  place_id INT NOT NULL UNIQUE,
  place_type VARCHAR(10) DEFAULT 'country' NOT NULL
     CHECK ( place_type = 'country' ),
  FOREIGN KEY ( place_type, place_id ) 
     REFERENCES places ( place_type, place_id )
  -- attributes specific to country go here
);

CREATE TABLE popular_areas (
  user_id INT NOT NULL,
  place_id INT NOT NULL,
  UNIQUE ( user_id, place_id ),
  FOREIGN KEY ( place_type, place_id ) 
     REFERENCES places ( place_type, place_id )
);

这种设计不能保证地方的每一行都存在州或国家的一行(但不能两者都存在)。这是SQL中外键的一个局限性。在一个完全符合SQL-92标准的DBMS中，你可以定义可延迟的表间约束，这将允许你实现同样的目标，但它很笨拙，涉及事务，这样的DBMS还没有推向市场。

我意识到这个帖子很旧了，但我看到了这个，我想到了一个解决方案，我想我应该把它扔出去。

区域、国家和州是存在于层次结构中的地理位置。

您可以通过创建一个名为geography _location_type的域表来完全避免这个问题，您可以用三行(Region、Country、State)填充该表。

接下来，创建一个具有外键geography _location_type_id的地理位置表，而不是三个位置表(这样您就知道实例是Region、Country还是State)。

通过使这个表自引用来建模层次结构，这样一个State实例将fKey保存到它的父Country实例，而Country实例又将fKey保存到它的父Region实例。Region实例的fKey值为NULL。这与您使用三个表所做的没有什么不同(您将在地区和国家之间以及国家和州之间有1 -许多关系)，只是现在它们都在一个表中。

popul_user_location表将是user和georgraphical_location之间的范围解析表(因此许多用户可能喜欢许多地方)。

如此如此……

CREATE TABLE [geographical_location_type] (
    [geographical_location_type_id] INTEGER NOT NULL,
    [name] VARCHAR(25) NOT NULL,
    CONSTRAINT [PK_geographical_location_type] PRIMARY KEY ([geographical_location_type_id])
)

-- Add 'Region', 'Country' and 'State' instances to the above table


CREATE TABLE [geographical_location] (
   [geographical_location_id] BIGINT IDENTITY(0,1) NOT NULL,
    [name] VARCHAR(1024) NOT NULL,
    [geographical_location_type_id] INTEGER NOT NULL,
    [geographical_location_parent] BIGINT,  -- self referencing; can be null for top-level instances
    CONSTRAINT [PK_geographical_location] PRIMARY KEY ([geographical_location_id])
)

CREATE TABLE [user] (
    [user_id] BIGINT NOT NULL,
    [login_id] VARCHAR(30) NOT NULL,
    [password] VARCHAR(512) NOT NULL,
    CONSTRAINT [PK_user] PRIMARY KEY ([user_id])
)


CREATE TABLE [popular_user_location] (
    [popular_user_location_id] BIGINT NOT NULL,
    [user_id] BIGINT NOT NULL,
    [geographical_location_id] BIGINT NOT NULL,
    CONSTRAINT [PK_popular_user_location] PRIMARY KEY ([popular_user_location_id])
)

ALTER TABLE [geographical_location] ADD CONSTRAINT [geographical_location_type_geographical_location] 
    FOREIGN KEY ([geographical_location_type_id]) REFERENCES [geographical_location_type] ([geographical_location_type_id])



ALTER TABLE [geographical_location] ADD CONSTRAINT [geographical_location_geographical_location] 
    FOREIGN KEY ([geographical_location_parent]) REFERENCES [geographical_location] ([geographical_location_id])



ALTER TABLE [popular_user_location] ADD CONSTRAINT [user_popular_user_location] 
    FOREIGN KEY ([user_id]) REFERENCES [user] ([user_id])



ALTER TABLE [popular_user_location] ADD CONSTRAINT [geographical_location_popular_user_location] 
    FOREIGN KEY ([geographical_location_id]) REFERENCES [geographical_location] ([geographical_location_id])

不确定目标DB是什么;以上是MS SQL Server。

嗯，我有两张表:

歌曲

a)歌号 b)歌名 ．...

播放列表 a)播放列表编号 b)播放列表标题 .．.

我有第三个

songs_to_playlist_relation

问题是某些类型的播放列表有链接到其他播放列表。但在mysql中，我们没有与两个表相关联的外键。

我的解决方案:我将在songs_to_playlist_relation中放入第三列。这一列是布尔的。如果1然后歌曲，否则将链接到播放列表表。

So:

songs_to_playlist_relation

a) Playlist_number (int) b) Is song (boolean) c)相对数(歌曲号或播放列表号)(int)(不是任何表的外键)

#create table songs queries.append("SET SQL_MODE = NO_AUTO_VALUE_ON_ZERO;") queries.append("CREATE TABLE songs (NUMBER int(11) NOT NULL,SONG POSITION int(11) NOT NULL,PLAY SONG tinyint(1) NOT NULL DEFAULT '1',SONG TITLE varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,DESCRIPTION varchar(1000) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,ARTIST varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'Άγνωστος καλλιτέχνης',AUTHOR varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'Άγνωστος στιχουργός',COMPOSER varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'Άγνωστος συνθέτης',ALBUM varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'Άγνωστο άλμπουμ',YEAR int(11) NOT NULL DEFAULT '33',RATING int(11) NOT NULL DEFAULT '5',IMAGE varchar(600) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,SONG PATH varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,SONG REPEAT int(11) NOT NULL DEFAULT '0',VOLUME float NOT NULL DEFAULT '1',SPEED float NOT NULL DEFAULT '1') ENGINE=InnoDB DEFAULT CHARSET=utf8;") queries.append("ALTER TABLE songs ADD PRIMARY KEY (NUMBER), ADD UNIQUE KEY POSITION (SONG POSITION), ADD UNIQUE KEY TITLE (SONG TITLE), ADD UNIQUE KEY PATH (SONG PATH);") queries.append("ALTER TABLE songs MODIFY NUMBER int(11) NOT NULL AUTO_INCREMENT;")

#create table playlists
queries.append("CREATE TABLE `playlists` (`NUMBER` int(11) NOT NULL,`PLAYLIST POSITION` int(11) NOT NULL,`PLAYLIST TITLE` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,`PLAYLIST PATH` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;")
queries.append("ALTER TABLE `playlists` ADD PRIMARY KEY (`NUMBER`),ADD UNIQUE KEY `POSITION` (`PLAYLIST POSITION`),ADD UNIQUE KEY `TITLE` (`PLAYLIST TITLE`),ADD UNIQUE KEY `PATH` (`PLAYLIST PATH`);")
queries.append("ALTER TABLE `playlists` MODIFY `NUMBER` int(11) NOT NULL AUTO_INCREMENT;")

#create table for songs to playlist relation
queries.append("CREATE TABLE `songs of playlist` (`PLAYLIST NUMBER` int(11) NOT NULL,`SONG OR PLAYLIST` tinyint(1) NOT NULL DEFAULT '1',`RELATIVE NUMBER` int(11) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;")
queries.append("ALTER TABLE `songs of playlist` ADD KEY `PLAYLIST NUMBER` (`PLAYLIST NUMBER`) USING BTREE;")
queries.append("ALTER TABLE `songs of playlist` ADD CONSTRAINT `playlist of playlist_ibfk_1` FOREIGN KEY (`PLAYLIST NUMBER`) REFERENCES `playlists` (`NUMBER`) ON DELETE RESTRICT ON UPDATE RESTRICT")

这是所有!

playlists_query = "SELECT s1.*, s3.*, s4.* FROM songs as s1 INNER JOIN `songs of playlist` as s2 ON s1.`NUMBER` = s2.`RELATIVE NUMBER` INNER JOIN `playlists` as s3 ON s3.`NUMBER` = s2.`PLAYLIST NUMBER` INNER JOIN `playlists` as s4 ON s4.`NUMBER` = s2.`RELATIVE NUMBER` ORDER BY s3.`PLAYLIST POSITION`,`s1`.`SONG POSITION`"

关系的答案

注意mysql标记，它意味着关系，因为SQL是在Codd的关系模型中定义的数据子语言。

The solution is simple and straight-forward, we had it before the RM, and we have had a Relational solution since 1981. The Relational solution provides both Referential Integrity (physical, at the SQL level) and Relational Integrity (logical). To comply with Open Architecture Standards (sanity), all Constraints; business rules; etc that govern the data, as well as all Transactions, should be deployed in the database, not the framework, not the app GUI, not the app middle-tier.  Note that it is a single recovery unit.

多态关联标签是假的，op没有请求它。将其强制带入OO/ORM思维模式，然后在这种思维模式下证明解决方案，超出了问题的范围。

此外，它需要一个框架和代码来实施约束;等等，在数据库之外，这是不合标准的。 此外，它不具有关系解决方案的基本完整性，更不用说关系完整性了。 此外，它违反了1NF和3NF(详见Karvan Answer)。 null是一个归一化错误，它们不应该被存储。 一个可为空的外键是一个严重的正常化错误。

解决方案

这是我的问题，我有三张表;地区、国家、州。国家可以在区域内，国家可以在区域内。区域是食物链的顶端。

要有关系

让我们来理解在关系上下文中这是什么。它是典型的表层次结构。

Do not use ID fields.  Do not declare them as PRIMARY KEY, that will only confuse you, becasue it is not a Key, it does not provide row uniqueness as demanded in the Relational Model A Key must be made up from the data An ID field is not data.  It is always an additional field, and an additional index With ID fields, you might be able to implement Referential Integrity (physical, SQL), but you have no chance of implementing Relational Integrity (logical) For full discussion, including SQL code, refer to: Creating a Relational table with 2 different auto_increment, §1 & 2 only.

基表

符号

All my data models are rendered in IDEF1X, the notation for Relational Data Modelling, which we have had since the early 1980’s, made the Standard for Relational Data Modelling, in 1993, last updated 2016. The IDEF1X Introduction is essential reading for those who are new to the Relational Model, or its modelling method.  Note that IDEF1X models are rich in detail and precision, showing all required details, whereas a home-grown model, being unaware of the imperatives of the Standard, have far less definition.  Which means, the notation needs to be fully understood.   ERD is not a Standard, it does not support the Relational Model, and it is completely inadequate for modelling. That the academics and "textbooks" teach and market anti-Relational as "relational" is criminal.

子类型

现在我添加了一个有两列的popul_areas表;Region_id和popul_place_id。是否有可能使popul_place_id成为国家或州的外键。

没问题。关系模型是建立在数学基础上的逻辑，完全是逻辑。或或门是逻辑的基础。在关系或SQL范例中，它被称为子类型集群。

Even in freeware "SQLs", which are not SQL compliant, it is done with full Referential Integrity the notion that it can't be done, or that it requires the horrendous additional fields and indices marketed by the academics, is false. For full implementation details, including links to SQL code, refer to the Subtype document. For examples and discussion, refer to: How to Implement Referential Integrity in Subtypes For clarification of issues that confuse this Question, and thus the other Answers: Relational schema for a book graph

我可能必须添加一个popul_place_type列，以确定id是描述一个国家还是一个州。

正确，你的思维很有逻辑。这里我们需要一个异或门，这需要一个鉴别器。

添加位置表

关系完整性

参考完整性是SQL中提供的物理特性，而关系完整性是逻辑的，是在它之上的(当建模正确时，逻辑的先于物理的)。

这是关系完整性的一个很好的、简单的例子。注意子类型中的第二个外键。

PlaceCountry is constrained to a Country that is in the same Region as Place.Region PlaceState is constrained to a State that is in the same Region as Place.Region Note that this is possible only with Relational Keys (composite) Relational Integrity is not possible in the primitive Record Filing Systems, which are characterised by ID fields as "keys", and heavily marketed by the academics and authors as "relational" In such primitive files (they are not tables), PlaceCountry would allow any Country, it cannot be constrained to a Country that is in the same Region as Place.Region.