Postgresql – the correct way to map relationships between structurally similar comments tables and commentable entities

duplicationpostgresqlreferential-integrity

I'm working on the usual comments table in postgres that has a one-to-many relationship with a commentable entity. In other words, one comment belongs to a post, and a post can have many comments.

The problem is that I have multiple commentable entities. My initial thought was to create a table for each entity, like posts_comments, and profile_comments, but that just feels wrong as all the comments are structurally similar. If I decide to allow commenting on say, user-posted pictures, then I would need to add another picture_comments table with identical columns. And then if I decide to add or remove any column, I would have to modify all the tables for consistency.

So I'm considering whether I should instead have a single comments table and then maintain referential integrity through intermediary join tables with a unique index on the comment_id foreign key to ensure the one-to-many relationship. Are there any performance or size-related downsides from doing this?

What is the best way to handle this in postgres or SQL in general? I looked around for solutions or clues on stackoverflow and dba exchange but did not find a satisfying answer. If someone can provide an answer or opinion or a link to an already answered question, I would really appreciate it.

Best Answer

I think you should really look into using inheritance to manage this particular issue.

Inheritance

Table inheritance does many things in PostgreSQL, and I'd highly recommend you refer to the online documentation about inheritance to get a feel for what it does and how it might work for you.

Since you stated that your comments are all structurally similar, then you can make a parent comments table as

CREATE TABLE comments (
/* columns here */
);

Then you can create the child comment tables as (for example)

CREATE TABLE posts_comments (
/* any columns unique to this table here */
) INHERITS(comments);

Now, posts_comments will have all columns from comments, plus any additional column fields which you believe are necessary for that particular table. Rinse and repeat to make other tables which are children of the comments table.

Further, you can then query for all comments which match a particular condition

SELECT * FROM comments
WHERE condition = true;

and all comments meeting you condition will be returned. That is, every comment from the comments table (if there are any entries, since this might just be an empty parent table), plus all comments from the child tables posts_comments, profile_coments, etc. will be returned by the SELECT.

You can also have ALTER TABLE propagate changes to all child tables through this structure.

Does this meet your needs?

Related Solutions

Sql-server – Do I need a separate Id column for this “mapping” table

If you have a one-to-many relationship between Producers and Products (in other words, a product can only belong to one producer), than it would make sense to just put a foreign key reference directly in your Products table:

One-To-Many

create table Producer ( id int identity(1, 1) not null primary key clustered, Name varchar(100) not null ) go create table Product ( id int identity(1, 1) not null, Name varchar(100) not null, ProducerId int not null foreign key references Producer(id) ) go

But if there's a chance that this will be a many-to-many relationship, then your best bet would be to use a Join table.

Many-To-Many

create table Producer
(
    id int identity(1, 1) not null primary key clustered,
    Name varchar(100) not null
)
go

create table Product
(
    id int identity(1, 1) not null primary key clustered,
    Name varchar(100) not null
)
go

create table ProductProducer
(
    ProductId int not null foreign key references Product(id),
    ProducerId int not null foreign key references Producer(id)
)
go

-- adding the primary key also ensures uniqueness
alter table ProductProducer
add constraint PK_ProductProducer 
primary key (ProductId, ProducerId)
go

If you decide to go with the Join table, you wouldn't need to have an additional key, as the combination of ProductId/ProducerId would ultimately be unique. You could use them as a composite key, so you wouldn't need that additional Id field in ProductProducer.

Are there DBMS that allow a Foreign Key that References a View (and not only base tables)

This business rule can be enforced in the model using only constraints. The following table should solve your problem. Use it instead of your view:

    CREATE TABLE BookAspectCommonTagLink
    (  BookID INT NOT NULL
    , AspectID INT NOT NULL
    , TagID INT NOT NULL
--TagID is deliberately left out of PK
    , PRIMARY KEY (BookID, AspectID)
    , FOREIGN KEY (BookID, TagID) 
        REFERENCES BookTag (BookID, TagID)
    , FOREIGN KEY (AspectID, TagID) 
        REFERENCES AspectTag (AspectID, TagID)
    ) ;

Best Answer

Inheritance

Related Solutions

Sql-server – Do I need a separate Id column for this “mapping” table

Are there DBMS that allow a Foreign Key that References a View (and not only base tables)

Related Question