Polymorphic table in PostgreSQL

Question

I'm looking for guidance around a pattern I frequently encounter in my PostgreSQL table design. I want to know if I'm thinking about this wrong or if maybe this is a Relational DB limit.

I have a tables like this

CREATE TABLE feature 
(
    id serial PRIMARY KEY,
    name text NOT NULL,
    type text NOT NULL,
);

CREATE TABLE feature_type 
(
    value text PRIMARY KEY,
    comment text
);

ALTER TABLE feature ADD CONSTRAINT
  feature_type_fkey FOREIGN KEY (type) REFERENCES feature_type;

INSERT INTO feature_type (value, comment) VALUES
  ('OBJECT_TRACKING', 'Tracks a basic object.'),
  ('3D_OBJECT_TRACKING', 'Will track a 3D object'),
  ('TRIANGLE_TRACKING', 'Track a rectangular object');

I essentially have enforceable types for my feature table now. My problem is that now I want "settings" for any given feature type. Here is how I currently accomplish this.

CREATE TABLE object_tracking_settings (
  id serial PRIMARY KEY,
  tracking_radius INT NOT NULL,
);

CREATE TABLE three_d_object_tracking_settings (
  id serial PRIMARY KEY,
  object_depth_value INT NOT NULL,
);

CREATE TABLE triangle_tracking_settings (
  id serial PRIMARY KEY,
  corner_comparison INT NOT NULL,
);

ALTER TABLE feature
  ADD COLUMN object_tracking_settings_id uuid NOT NULL,
  ADD COLUMN three_d_object_tracking_settings_id uuid NOT NULL,
  ADD COLUMN triangle_tracking_settings_id uuid NOT NULL;

ALTER TABLE feature ADD CONSTRAINT
  object_tracking_settings_fkey FOREIGN KEY (object_tracking_settings_id) REFERENCES object_tracking_settings;

ALTER TABLE feature ADD CONSTRAINT
  three_d_object_tracking_settings_fkey FOREIGN KEY (three_d_object_tracking_settings_id) REFERENCES three_d_object_tracking_settings;

ALTER TABLE feature ADD CONSTRAINT
  triangle_tracking_settings_fkey FOREIGN KEY (triangle_tracking_settings_id) REFERENCES triangle_tracking_settings;

Now my client side applications can get all the feature's, switch on the type and obtain the appropriate settings. Is there a better way to do this? I guess I could call this "polymorphism" in a relational DB. Should I just be breaking each feature out into it's own table?

Guesthouse

Answer 1

Polymorphic associations can be difficult to correctly represent in a relational database. To achieve that, you have :

1. Polymorphic Joins :a serious problem with this approach is the database is very limited in the data integrity it can enforce due to the lack of foreign key constraints.
2. Join Table Per Relationship Type : This approach does use foreign key constraints, so the database can ensure that any connections between the tables
3. Reverse Belongs-To : Even though for example the object_tracking_settings logically belongs to the feature, this relationship can be reversed by including a feature_id on the object_tracking_settings. This is similar in implementation to class table inheritance, but the relationship is more of inclusion rather than is-a.

Also, Inheritance with PostgreSQL is possible since it is an .object-oriented database Inheritance is a concept from object-oriented databases.

CREATE TABLE feature (
  id serial PRIMARY KEY,
  name text NOT NULL,
  type text NOT NULL,
);

CREATE TABLE object_tracking_settings (
  tracking_radius INT NOT NULL,
) INHERITS(feature);

Jaquenette

Inheritance is not something I have tried in PostgreSQL yet. I think I need to learn a little more but this is an interesting approach. Could "Views" also maybe server a polymorphic purpose? – Guesthouse

@JonVogel Views cannot inherit.Only tables can. – Jaquenette

I understand, I was thinking that a view could effectively combine field from multiple tables serving as a pseudo polymorphic object. – Guesthouse

Answer 2

My suggestion is for a hybrid (relational + document) design - store tracking_settings for all feature_type-s in a JSONB column of a single table. This would give you the best of both worlds - keys in a strict relational structure and non-key details in flexible document structures.

CREATE TABLE tracking_settings (
  id serial PRIMARY KEY,
  j_setting JSONB NOT NULL
);

ALTER TABLE feature
  ADD COLUMN tracking_settings_id integer NOT NULL;

ALTER TABLE feature ADD CONSTRAINT tracking_settings_fkey
  FOREIGN KEY (tracking_settings_id) REFERENCES tracking_settings(id);

Then for object tracking settings you may

insert into tracking_settings (j_settings) values ('{"tracking_radius": 100}') returning id;

and for three_d_object racking settings -

insert into tracking_settings (j_settings) values ('{"object_depth_value": 101}') returning id;

You have the additional benefit to have more and even different number of attributes for tracking_settings of different feature types w/o changing the db schema.
You can still enforce consistency with check constraints that ensure that j_settings contains the right keys/values.

• Edit

Since tracking_settings logically belong to feature you can avoid tracking_settings table and simply

ALTER TABLE feature
  ADD COLUMN tracking_settings JSONB NOT NULL; 

BTW since you have a small and fixed set of feature types you may consider using an enum type for feature.type field instead of a foreign key and feature_type table. Finally all that's left is

CREATE TABLE feature (
  id serial PRIMARY KEY,
  name text NOT NULL,
  type text feature_type NOT NULL, -- feature_type is enum type
  tracking_settings JSONB NOT NULL
);

Alcaeus

Thank you, I like this strategy. Just requires a little client side parsing. – Guesthouse