这是我的问题，我有三张表;地区、国家、州。国家可以在区域内，国家可以在区域内。区域是食物链的顶端。
现在我添加了一个有两列的popul_areas表;Region_id和popul_place_id。是否有可能使popul_place_id成为国家或州的外键。我可能必须添加一个popul_place_type列，以确定id是描述一个国家还是一个州。
当前回答
嗯，我有两张表:
歌曲
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`"
其他回答
这不是世界上最优雅的解决方案，但是您可以使用具体的表继承来实现这一点。
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非常详细地解释了为什么多态关联不是你的朋友。
关系的答案
注意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.
嗯，我有两张表:
歌曲
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`"
以下是对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。
