Polymorphism in SQL database tables?

Question

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_series has the fields season, episode, airing, while the movies table has release_date, budget etc.

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.

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.

Answer 1

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.

Answer 2

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

Answer 3

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.

Answer 4

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 character varchar(100),
  description    text,
  url character  varying(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.

Answer 5

Since you tagged this PostgreSQL, you could look at http://www.postgresql.org/docs/8.1/static/ddl-inherit.html but beware the caveats.

Answer 6

Using the disjoint subtype approach suggested by Bill Karwin, how would you do INSERTs and UPDATEs without having to do it in two steps?

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.

Thanks