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.
Best Answer
If you really want to avoid NULLs, go with the inheritance.
Unless you want to query all posts with all replies all the time, I think joins won't be a problem if you index your tables.