I have a sort of blog application that I'm working on for constrained writing. The posts have several types, and share some columns in common (title, body for instance), but they also have columns that they don't share in common. Please correct me if I'm wrong, but I feel like my two database design options are:
- Create a separate table for each type of post (and then use UNION if I ever need to select all of the various types).
- Create one table to store all of the similar columns (and then have several one-to-one relationships to define what's different about them--like an inheritance-type situation).
Which is better? Why? Are there other designs that would be better?
UPDATE: these posts will be different types of constrained writing (I.e., poems), that are validated in specific ways based on the particular constraint technique, and may have a variety of other tables associated with them, so extensibility will be really important. That said it sounds like either the nosql or inheritance options would be best. I would just be worried about doing analytical stuff with nosql (never used it, so I just don't know anything about how to aggregate, etc, with it).
never used it, so I just don't know anything about how to aggregate, etc, with it
If you don't have the time to learn, don't go with a solution you're unfamiliar with.