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 对其进行规范化，但我不喜欢这个想法，因为它是一种内部数据库"-a-database' 方案，我没有办法要求某些键/字段，也没有要求特殊类型，以后获取和排序数据将是一个巨大的痛苦.

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),
  unique 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屋！