Полиморфизм в таблицах базы данных 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, но мне не нравится эта идея, поскольку она является своего рода схемой "база данных в базе данных", у меня нет способа требовать определенные ключи / поля и не требовать специального типа, и это будет быть огромной болью, чтобы получить и заказать данные позже.

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

Источник

7 ответов

Решение

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

Замечания @Counbly Infinite и @Peter:

Вставка в две таблицы потребует двух операторов вставки. Но это также верно в 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.

Источник

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

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
Источник

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

Источник

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

Источник

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

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

Спасибо

Источник

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

Источник

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

create table some_table (
    name character varying(100),
    description text,
    url character varying(255),
    additional_data json
);
Источник
Другие вопросы по тегам