Multiple columns (one per type) vs. single TEXT/Clob column

Question

I need to store some diffs of other tables' columns in the database. I want to use a single table.

Option A

Table with 4 columns:

• id
• content_type (VARCHAR 255, will be: datetime, integer, varchar, text/clob ...)
• old_content (CLOB / TEXT, software serialized data for content-type)
• new_content (CLOB / TEXT, software serialized data for content-type)

Option B

Table with ~10+ columns:

• id
• content_type (VARCHAR 255)
• old_datetime (DATETIME)
• new_datetime (DATETIME)
• old_integer (INTEGER)
• new_integer (INTEGER)
• old_string (VARCHAR 255)
• new_string (VARCHAR 255)
• old_text (CLOB / TEXT)
• new_text (CLOB / TEXT)
• ...

Only one tuple will be set at a time, the others will be NULL.

Evaluation

At the moment I would prefer Option B - because I'm not a huge fan of serialized data in a RDBMS. However I don't know whether databases (in my case MySQL, maybe Postgre) would be better at handling (compressing) Option A in contrast to Option B.

I have seen approach B in some other projects (JBoss jBPM) and also feel more comfortable with B, but I'm ready to be lectured.

Would you use A or B and why? What are the tradeoffs of each option? Or is there maybe a C?

Other stuff that might be relevant:

• For the current project, I assume that there will be more data in the non-TEXT/CLOB columns.
• I do not want to search through the non-ID columns, so no additional Index is needed.

Answer 1

This is not a full response to your question, but take a look at "Column Sets" in SQL 2008. You can use it for OPTION A ... and make it look like OPTION B.

Answer 2

Well, there is one more solution, more tables with supertype/subtype structure. The good thing about this one is that there are no extra nulls, for example:

TABLE DataChange (ID (PK), content_type, ChangeTime)
TABLE IntChange (ID (PK=FK), old_value int, new_value int)
TABLE StrChange (ID (PK=FK), old_value varchar(255), new_value varchar(255))
TABLE TxtChange (ID (PK=FK), old_value text, new_value text)
...

IntChange, StrChange, and TxtChange are types of DataChange, so ID serves as the primary and the foreign key and is the same number as the ID in the DataChange table. The content_type is the classifier column. Here is a model of a similar (supertype/subtype) example.