Using PostgreSQL 11.
I've tried digging through both Stack Overflow and here and was unable to find an answer on best practices.
I'm working on a database design and have arrived at a schema that uses a generic "join table". This join table contains five columns:
CREATE TABLE many_joins_table ( id PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, object_id int NOT NULL, object_table joins_object_t NOT NULL, parent_id int NOT NULL, parent_table joins_parent_t NOT NULL);
I've been using this table to represent adjacent and many-to-many relationships between objects in my database. One such example is tags.
CREATE TABLE tag ( id PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, name text NOT NULL UNIQUE); CREATE TABLE comment ( id PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY); CREATE TYPE joins_object_t AS ENUM ('tag'); CREATE TYPE joins_parent_t AS ENUM ('comment');
When a tag is added to the comment table, I would insert a new row into this join table with the following fields:
INSERT INTO many_joins_table VALUES(1, 'tag'::joins_object_t, 1, 'comment'::joins_parent_t);
Besides inflexibility of enums, addressed with PostgreSQL 9.1 https://stackoverflow.com/questions/1771543/adding-a-new-value-to-an-existing-enum-type/7834949#7834949.
Are there any significant disadvantages or advantages of such an approach? I'm worried that I've mistakenly implemented an anti-pattern. Are there any best practices I can apply to improve this implementation (indexing or constraints)?
Note: I'm aware there are better ways to implementing tags, namely using intarrays. I'm just using tags as an example since it's easily understood. https://stackoverflow.com/questions/23508551/integer-array-lookup-using-postgres
Edit: Removed the UUIDs since it may be a distraction to the question.