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.
"polymorphism" means multiple shapes (multiple subprograms, same name). Overloading is static polymorphism because the COMPILER resolves which of the subprograms to execute (at compile time). Dynamic polymorphism means we have 2+ methods with the same name, but in different types in the same hierarchy.
Polymorphic association is a term used in discussions of Object-Relational Mapping with respect to the problem of representing in the relational database domain, a relationship from one class to multiple classes. In statically typed languages such as Java these multiple classes are subclasses of the same superclass.
Polymorphic data means that in one collection you have many versions of document schema (e.g. different field type, fields that occur in some documents etc.).
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With