SQL数据库表中的多态性? [英] Polymorphism in SQL database tables?
问题描述
我目前在数据库中有多个表，这些表由相同的基本字段"组成，例如:
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)但是我对该基本表有多种专长，例如，tv_series具有字段season，episode，airing，而movies表具有release_date，budget等
But I have multiple specializations of that basic table, which is for example that tv_series has the fields season, episode, airing, while the movies table has release_date, budget etc.
现在起初这不是问题，但是我想创建第二个表，称为linkgroups，这些表具有这些专用表的外键.这意味着我将不得不以某种方式对其内部进行标准化.
Now at first this is not a problem, but I want to create a second table, called linkgroups with a Foreign Key to these specialized tables. That means I would somehow have to normalize it within itself.
解决这个问题的一种方法是使用key-value -pair-table对它进行规范化，但是我不喜欢这种想法，因为它是一种数据库内数据库"方案，我既没有要求某些键/字段的方法，也没有特殊类型的方法，以后再获取和排序数据将是一个巨大的痛苦.
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.
因此，我现在正在寻找一种在多个表之间甚至更好地共享"主键的方法:一种通过具有通用表和多个专用表来对其进行规范化的方法.
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.
推荐答案
对，问题是您只希望一种子类型的对象引用父类的任何给定行.从@Jay S给出的示例开始，尝试以下操作:
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 example given by @Jay S, try this:
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 mentioned by @mike g.
@Countably Infinite和@Peter的评论:
Re comments by @Countably Infinite and @Peter:
插入两个表需要两个插入语句.但是，只要您有子表，在SQL中也是如此.这是很平常的事情.
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.
UPDATE可能需要两个语句，但是某些品牌的RDBMS支持使用JOIN语法的多表UPDATE，因此您可以在一个语句中执行.
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.
查询数据时，如果只需要有关公共列的信息，则只需查询media表即可完成此操作:
When querying data, you can do it simply by querying the media table if you only need information about the common columns:
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 = ?
如果给定的媒体是电影，则t.*中的所有列都将为NULL.
If the given media is a movie,then all columns in t.* will be NULL.
这篇关于SQL数据库表中的多态性?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
