postgresql SQL 数据库表中的多态性？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/561576/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Polymorphism in SQL database tables?
提问by Patrick Glandien
I currently have multiple tables in my database which consist of the same 'basic fields' like:
我目前在我的数据库中有多个表，它们由相同的“基本字段”组成，例如：
name character varying(100),
description text,
url character varying(255)But I have multiple specializations of that basic table, which is for example that tv_serieshas the fields season, episode, airing, while the moviestable has release_date, budgetetc.
但我有一个基本的表，这是例子的多个专业化tv_series有田season，episode，airing，而movies表中有release_date，budget等等。
Now at first this is not a problem, but I want to create a second table, called linkgroupswith a Foreign Key to these specialized tables. That means I would somehow have to normalize it within itself.
现在起初这不是问题，但我想创建第二个表，linkgroups用这些专用表的外键调用。这意味着我必须以某种方式将其标准化。
One way of solving this I have heard of is to normalize it with a key-value-pair-table, but I do not like that idea since it is kind of a 'database-within-a-database' scheme, I do not have a way to require certain keys/fields nor require a special type, and it would be a huge pain to fetch and order the data later.
我听说解决这个问题的一种方法是用key-value-pair-table对其进行规范化，但我不喜欢这个想法，因为它是一种“数据库中的数据库”方案，我没有办法需要某些键/字段也不需要特殊类型，稍后获取和排序数据将是一个巨大的痛苦。
So I am looking for a way now to 'share' a Primary Key between multiple tables or even better: a way to normalize it by having a general table and multiple specialized tables.
所以我现在正在寻找一种方法来在多个表之间“共享”一个主键，甚至更好：一种通过拥有一个通用表和多个专用表来规范化它的方法。
回答by Bill Karwin
Right, the problem is you want only one object of one sub-type to reference any given row of the parent class. Starting from the examplegiven by @Jay S, try this:
是的，问题是您只希望一种子类型的一个对象引用父类的任何给定行。从@Jay S 给出的例子开始，试试这个：
create table media_types (
media_type int primary key,
media_name varchar(20)
);
insert into media_types (media_type, media_name) values
(2, 'TV series'),
(3, 'movie');
create table media (
media_id int not null,
media_type not null,
name varchar(100),
description text,
url varchar(255),
primary key (media_id, media_type),
foreign key (media_type)
references media_types (media_type)
);
create table tv_series (
media_id int primary key,
media_type int check (media_type = 2),
season int,
episode int,
airing date,
foreign key (media_id, media_type)
references media (media_id, media_type)
);
create table movies (
media_id int primary key,
media_type int check (media_type = 3),
release_date date,
budget numeric(9,2),
foreign key (media_id, media_type)
references media (media_id, media_type)
);
This is an example of the disjoint subtypes mentionedby @mike g.
这是@mike g提到的不相交子类型的一个例子。
Re comments by @Countably Infinite and @Peter:
@Countably Infinite 和 @Peter 的评论：
INSERT to two tables would require two insert statements. But that's also true in SQL any time you have child tables. It's an ordinary thing to do.
INSERT 到两个表将需要两个插入语句。但在 SQL 中任何时候都有子表时也是如此。这是一件很平常的事情。
UPDATE may require two statements, but some brands of RDBMS support multi-table UPDATE with JOIN syntax, so you can do it in one statement.
UPDATE 可能需要两条语句，但是一些品牌的 RDBMS 支持使用 JOIN 语法的多表 UPDATE，因此您可以在一条语句中完成。
When querying data, you can do it simply by querying the mediatable if you only need information about the common columns:
查询数据时，media如果只需要公共列的信息，只需查询表即可：
SELECT name, url FROM media WHERE media_id = ?
If you know you are querying a movie, you can get movie-specific information with a single join:
如果你知道你正在查询一部电影，你可以通过一个连接获得特定于电影的信息：
SELECT m.name, v.release_date
FROM media AS m
INNER JOIN movies AS v USING (media_id)
WHERE m.media_id = ?
If you want information for a given media entry, and you don't know what type it is, you'd have to join to all your subtype tables, knowing that only one such subtype table will match:
如果您需要给定媒体条目的信息，而您不知道它是什么类型，则必须加入所有子类型表，因为知道只有一个这样的子类型表会匹配：
SELECT m.name, t.episode, v.release_date
FROM media AS m
LEFT OUTER JOIN tv_series AS t USING (media_id)
LEFT OUTER JOIN movies AS v USING (media_id)
WHERE m.media_id = ?
If the given media is a movie,then all columns in t.*will be NULL.
如果给定的媒体是电影，则所有列都t.*将为 NULL。
回答by Jay S
Consider using a main basic data table with tables extending off of it with specialized information.
考虑使用一个主要的基本数据表，其中的表带有专门的信息。
Ex.
前任。
basic_data
id int,
name character varying(100),
description text,
url character varying(255)
tv_series
id int,
BDID int, --foreign key to basic_data
season,
episode
airing
movies
id int,
BDID int, --foreign key to basic_data
release_data
budget
回答by MkV
Since you tagged this PostgreSQL, you could look at http://www.postgresql.org/docs/8.1/static/ddl-inherit.htmlbut beware the caveats.
由于您标记了这个 PostgreSQL，您可以查看http://www.postgresql.org/docs/8.1/static/ddl-inherit.html但要注意警告。
回答by mike g
What you are looking for is called 'disjoint subtypes' in the relational world. They are not supported in sql at the language level, but can be more or less implemented on top of sql.
您正在寻找的在关系世界中被称为“不相交的子类型”。sql 在语言级别不支持它们，但可以或多或少地在 sql 之上实现。
回答by Chris Simpson
You could create one table with the main fields plus a uid then extension tables with the same uid for each specific case. To query these like separate tables you could create views.
您可以创建一个包含主要字段和 uid 的表，然后为每种特定情况创建具有相同 uid 的扩展表。要像单独的表一样查询这些，您可以创建视图。
回答by Peter
Using the disjoint subtype approach suggested by Bill Karwin, how would you do INSERTs and UPDATEs without having to do it in two steps?
使用 Bill Karwin 建议的不相交子类型方法，您将如何执行 INSERT 和 UPDATE 而不必分两步完成？
Getting data, I can introduce a View that joins and selects based on specific media_type but AFAIK I cant update or insert into that view because it affects multiple tables (I am talking MS SQL Server here). Can this be done without doing two operations - and without a stored procedure, natually.
获取数据，我可以引入一个基于特定 media_type 加入和选择的视图，但 AFAIK 我无法更新或插入到该视图中，因为它影响多个表（我在这里说的是 MS SQL Server）。这可以在不做两个操作的情况下完成 - 并且没有存储过程，自然。
Thanks
谢谢
回答by Radek Posto?owicz
Question is quite old but for modern postresql versions it's also worth considering using json/jsonb/hstore type. For example:
问题很老，但对于现代 postresql 版本，也值得考虑使用 json/jsonb/hstore 类型。例如：
create table some_table (
name character varying(100),
description text,
url character varying(255),
additional_data json
);
