Полиморфизм в таблицах базы данных SQL?

В настоящее время у меня есть несколько таблиц в моей базе данных, которые состоят из тех же "базовых полей", что:

name character varying(100),
description text,
url character varying(255)

Но у меня есть несколько специализаций этой основной таблицы, например, что tv_series имеет поля season, episode, airing, а таблица movies имеет release_date, budget и др.

Теперь сначала это не проблема, но я хочу создать вторую таблицу под названием linkgroups с внешним ключом для этих специализированных таблиц. Это означает, что мне как-то придется нормализовать его внутри себя.

Один из способов решения этого, о котором я слышал, - это нормализовать его с помощью таблицы key-value -pair-table, но мне не нравится эта идея, поскольку это своего рода схема "база данных внутри базы данных", У меня нет способа требовать определенные ключи/поля и не требуется специальный тип, и было бы больно избавиться и упорядочить данные позже.

Итак, теперь я ищу способ "поделиться" Первичным ключом между несколькими таблицами или даже лучше: способ нормализовать его, имея общую таблицу и несколько специализированных таблиц.

Ответ 1

Правильно, проблема в том, что вы хотите, чтобы только один объект одного подтипа ссылался на любую заданную строку родительского класса. Начиная с примера заданного @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)
);

Это пример непересекающихся подтипов упомянутых @mike g.

Re комментарии от @Countably Infinite и @Peter:

В INSERT для двух таблиц потребуются два оператора insert. Но это также верно в SQL в любое время, когда у вас есть дочерние таблицы. Это обычная вещь.

UPDATE может потребовать два оператора, но некоторые бренды RDBMS поддерживают многозадачный UPDATE с синтаксисом JOIN, поэтому вы можете сделать это в одном из утверждений.

При запросе данных вы можете сделать это просто, запросив таблицу media, если вам нужна только информация об общих столбцах:

SELECT name, url FROM media WHERE media_id = ?

Если вы знаете, что вы запрашиваете фильм, вы можете получить информацию о конкретном видео с помощью одного соединения:

SELECT m.name, v.release_date
FROM media AS m
INNER JOIN movies AS v USING (media_id)
WHERE m.media_id = ?

Если вам нужна информация для данной записи мультимедиа, и вы не знаете, какой тип она есть, вам придется присоединиться ко всем таблицам подтипов, зная, что будет соответствовать только одна такая таблица подтипов:

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.

Ответ 2

Рассмотрите возможность использования основной базовой таблицы данных с таблицами, простирающимися от нее со специализированной информацией.

Исх.

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

Ответ 3

Поскольку вы отметили этот PostgreSQL, вы можете посмотреть http://www.postgresql.org/docs/8.1/static/ddl-inherit.html, но остерегайтесь оговорок.

Ответ 4

То, что вы ищете, называется "непересекающиеся подтипы" в реляционном мире. Они не поддерживаются в sql на уровне языка, но могут быть более или менее реализованы поверх sql.

Ответ 5

Вы можете создать одну таблицу с основными полями плюс uid, а затем таблицы расширения с одинаковым uid для каждого конкретного случая. Чтобы запросить их как отдельные таблицы, вы могли создавать представления.

Ответ 6

Используя подход непересекающегося подтипа, предложенный Биллом Карвином, как бы вы делали INSERT и UPDATE без необходимости делать это в два этапа?

Получение данных, я могу представить представление, которое объединяется и выбирает на основе определенного типа media_type, но AFAIK я не может обновить или вставить в это представление, потому что оно влияет на несколько таблиц (я говорю здесь о MS SQL Server). Это можно сделать без выполнения двух операций - без сохранения хранимой процедуры.

Спасибо

Ответ 7

Вопрос довольно старый, но для современных версий postresql также стоит рассмотреть использование типа json/jsonb/hstore. Например:

create table some_table (
    name character varying(100),
    description text,
    url character varying(255),
    additional_data json
);