CODEWITHSUNDEEP
relational-databasepolymorphic-associations
Tudor
Tudor

Reputation: 1181

What is best way to represent polymorphic associations in relational database?

Assuming this representation of the tables :

db parent/child tables

Object is the "parent" table that holds the objectids of all the other 4 child tables .
The 'something' table , besides having an objectid column , will also include linked_to_objectid column. This column points only to the objectid from object1 and object2 (not from object3) .

My problem is that i will have to check everytime when i insert a row , if the linked_to_objectid is not from object3 .

Another way would be to add another column to object table that would describe what type of object the objectid is ... But it i feel that this would be wrong .

I know this model breaks the normal form rules , but i can't find some other ways .
Anyone could help me and find the best way to model this ?

Upvotes: 2

Views: 1932

Answers (1)

Chris Travers
Chris Travers

Reputation: 26464

I think your answer is to use reciprocal primary/foreign keys and partition part of the primary key among your tables, something like:

 CREATE TABLE object_class (
     id int not null unique, -- hand assigned
     label text not null primary key
 );

 CREATE TABLE object (
     object_id bigserial not null primary key,
     class_id int not null references object_class(id),
     ...., 
     UNIQUE (object_id, class_id)
 );

 CREATE TABLE object1 (
      object_id bigint not null,
      class_id bigint not null,
      .....
      check(class_id = 1),
      primary key (object_id, class_id),
      foreign key (object_id, class_id) references object(object_id, class_id)
 );

 etc.

Now if you are using PostgreSQL, you can probably use table inheritance and constraint triggers to implement something a bit cleaner, but that's relatively advanced stuff.

Upvotes: 4

Related Questions