0

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.

4
  • 1
    Why do you want to avoid NULL values?
    – matigo
    May 25 at 14:36
  • 1
    Put posts in one table, replies in another. No fields would have to be null and you can optimise storage for both, FK replies to posts.
    – VynlJunkie
    May 25 at 14:40
  • How many columns do both tables have, and how many do they share? May 25 at 14:49
  • matigo, I don't have a particularly good reason, just seems like there would be a better alternative is all, I'm very unfamiliar with databases so I'm trying to get a feel for "the way of doing things", if that makes sense :D Vynl, I'm not sure I understand correctly but wouldn't that be almost identical to the approach I posted about in the first solution? Laurenz, there are 4 shared columns, and A has 1 extra, while B has 2 (for the time being). May 25 at 15:01
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.