Polymorphism in SQL database tables?

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.

Re comments by @Countably Infinite and @Peter:

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 may require two statements, but some brands of RDBMS support multi-table UPDATE with JOIN syntax, so you can do it in one statement.

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_dateFROM media AS mINNER 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_dateFROM media AS mLEFT OUTER JOIN tv_series AS t USING (media_id)LEFT OUTER JOIN movies AS v USING (media_id)WHERE m.media_id = ?

If the given media is a movie,then all columns in t.* will be NULL.

Consider using a main basic data table with tables extending off of it with specialized information.

Ex.

basic_dataid int,name character varying(100),description text,url character varying(255)tv_seriesid int,BDID int, --foreign key to basic_dataseason,episodeairingmoviesid int,BDID int, --foreign key to basic_datarelease_databudget

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