Say we have two kinds of posts, the first we'll call A and the second we'll call B. Both of them share columns, such as the submission date, etc. but also have distinct ones. Posts can also be replies - they have a
parent_id, which can either be null (doesn't reply to anything) or an id which refers to another post.
What I'm having trouble with is understanding the optimal solution to model such a system.
The obvious method would be to just put all of them inside a single table, which would work fine but you'd also get a ton of null values (e.g. an entry of type A will have NULL in every field of type B).
Another method, which I'm currently thinking about, would be to use inheritance, so something like this:
CREATE TABLE post ( post_id SERIAL PRIMARY KEY, submission_date TIMESTAMP NOT NULL, ... FOREIGN KEY (parent_id) REFERENCES post(post_id) ) CREATE TABLE table_A ( ... ) INHERITS (post) CREATE TABLE table_B ( ... ) INHERITS (post)
The problem with this approach is that from an entry in table A I'd have no way of finding the
parent_id (since it could either refer to B or A), so (to my knowledge) for every lookup I'd have to join all the tables and do it that way, which seems suboptimal.
I'm not sure if I'm missing something, any pointers to a potentially better solution would be much appreciated, thank you.