Polymorphism in SQL database tables?

by

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_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 = ?

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

More Related Contents:

  1. Select rows which are not present in other table
  2. Optimize GROUP BY query to retrieve latest row per user
  3. How to implement a many-to-many relationship in PostgreSQL?
  4. Aggregate columns with additional (distinct) filters
  5. Check if value exists in Postgres array
  6. Insert data in 3 tables at a time using Postgres
  7. How to use ANY instead of IN in a WHERE clause?
  8. Is there a shortcut for SELECT * FROM?
  9. sql statement error: “column .. does not exist”
  10. What is the difference between single quotes and double quotes in PostgreSQL?
  11. Concatenate multiple result rows of one column into one, group by another column [duplicate]
  12. sql ORDER BY multiple values in specific order?
  13. Constraint defined DEFERRABLE INITIALLY IMMEDIATE is still DEFERRED?
  14. Simulate CREATE DATABASE IF NOT EXISTS for PostgreSQL?
  15. Best way to count rows by arbitrary time intervals
  16. Sorting null values after all others, except special
  17. Reference alias in WHERE clause
  18. Fastest check if row exists in PostgreSQL
  19. PostgreSQL ORDER BY issue – natural sort
  20. Discard millisecond part from timestamp
  21. How do I cast a string to integer and have 0 in case of error in the cast with PostgreSQL?
  22. SQL column reference “id” is ambiguous
  23. How to use multiple CTEs in a single SQL query?
  24. IN Clause with NULL or IS NULL
  25. postgres – where in (list) – column does not exist
  26. incomplete information from query on pg_views [duplicate]
  27. How to get a list column names and datatypes of a table in PostgreSQL?
  28. Postgresql query between date ranges
  29. How to use SQL LIKE condition with multiple values in PostgreSQL?
  30. Is there a postgres CLOSEST operator?

Leave a Comment