What is meta? ×
Meta Stack Exchange is where users like you discuss bugs, features, and support issues that affect the software powering all 128 Stack Exchange communities.

Why does the revisions schema not store an entire post's contents in a single record, rather creating three records, one for each component of a post?

For example, if the post is of a type question, a new revisions record is created for each the title, the body, and the tags. The revisions are grouped with a revisionsGUID column. Would it not be simpler to store the entire post in a single history record?--instead of breaking out the post into three records, you can create a column for each component in the same record.

This would be no more redundant than the posts table, where answer posts leave the title and tags columns blank anyway.

Isn't that more efficient and less complex? Why run three queries and break the post data down by type? Doesn't this add complexity to the logic?

There are far brighter people who thought of this, but it's beyond me to see the advantages of modeling the data this way.

Can anyone elaborate as to why this is the case?

share|improve this question

1 Answer 1

up vote 7 down vote accepted

Why does the revisions schema not store an entire post's contents in a single record, rather creating three records, one for each component of a post?

The biggest reason is that if every PostHistory record contained all fields, that means post bodies would be put into every record -- this is by far the most space-consuming part of the table, so duplicating that data wastes far more storage space than any null column value, possibly even several hundred of them.

Also, putting everything into a single record makes finding the differences between revisions more complicated. Under the current system, if there's a single revision with type 4, let's say, then the application knows that the title was edited, without having to check the fields at all.

This would be no more redundant than the posts table, where answer posts leave the title and tags columns blank anyway.

The Posts and PostHistory tables only appear to contain roughly the same types of data. In fact, they are very different not only in what they store, but also their purpose in the application. Those two tables really aren't comparable.

Isn't that more efficient and less complex? Why run three queries and break the post data down by type? Doesn't this add complexity to the logic?

See above. Essentially, the current system stores a record for each field that has changed.

Also, I suspect revision data is returned by a single query, which would be something like this:

SELECT *
    FROM PostHistory
    WHERE PostId = 1234567890
    ORDER BY CreationDate DESC, RevisionGUID
share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .