3

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:

  1. Create a separate table for each type of post (and then use UNION if I ever need to select all of the various types).
  2. 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).

Improve this question
2
  • 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.
    – yannis
    Jan 4, 2012 at 16:22
  • sounds like complicated logic that should not be in the database at all to begin with
    – user7519
    Jan 4, 2012 at 20:27

3 Answers 3

Reset to default
3

I prefer the second option, the hierarchy. But it gets complicated very soon, and I don't really think you want that for a blog. You could of course go with a god table that fits all posts, and has a lot of null values for optional fields, but that won't really scale well.

I think the scenario, as you describe it, is perfect for a schema-less document-oriented storage, a NoSQL solution like MongoDB or Apache CrouchDB. There are others, of course, these are the two I'm more familiar with. You are looking for a flexible schema, what better solution from a schema-less one?

If a NoSQL solution is not an option, you should balance the relational options on potential complexity, maintainability, and scalability. If you don't have a pretty good idea of how your database will grow, it's going to be a very tough decision, but still one you'll have to make for yourself.

Improve this answer
7

Third option:

Create one table containing all columns and allow nulls for the non-common fields.

This will make for fast reads and remove the need for joins.

Whether it is better than your other options depends on your requirements.

Improve this answer
4
  • Making non-common fields generally nullable can lead to integrity problems, though. But as you said, it depends on the requirements.
    – ftr
    Jan 4, 2012 at 15:17
  • 3
    @ftr - One can add CHECK constraints or other mechanisms to ensure data integrity.
    – Oded
    Jan 4, 2012 at 15:19
  • I think this sounds like the simplest approach.
    – GrandmasterB
    Jan 4, 2012 at 19:59
  • Quite right. I rarely write DDL myself and forgot about CHECK.
    – ftr
    Jan 5, 2012 at 9:47
1

Another choice is SQL blobs. This is in some ways similar to the NoSQL approach, but you can stick to something you are more familiar with. If some of your data fits the relational model more easily than the particular data you are having a problem with, this lets you put everything in the same relational database.

You could, for example, put the extra fields that are not in common in XML blobs, or JSON blobs, or any other form of serialization you like.

You could also have some kind of Name/Value table to store each property in it's own row of data for any extra properties you need.

None of the options in this answer or the others so far (or anywhere, AFAIK) are 100% ideal, so you have to pick your poison.

I usually do the inheritance based structure, but might map more than one class onto the same physical table (allowing nulls, so combining Oded's suggestion with inheritance).

You are really going to need to encapsulate your data access layer given your requirements. Though even if you do, changing your mind later will be tough, since it will involve a data migration to your new structures.

Also, if you will hand-code a bunch of SQL queries based on your structure then your are that much more committed to it.

Improve this answer
2
  • 1
    Warning, though--bit serialized text fields always seem like a good idea until somebody asks you to search or sort by the "blobbed" values. And then you regret it. I have every single time. This is a classic "easy way out that bites you later" kind of deal.
    – Dan Ray
    Jan 4, 2012 at 21:25
  • @DanRay - Sure, but same issue with your cool NoSQL approach. At least this way you can keep everything in one database. But, I personally would not go the serialization method except under unusual circumstances.
    – psr
    Jan 4, 2012 at 21:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.