How bad is adding new column each time to compare the fixed values

sqlite

As for now we have a program that gets some data weekly which need to be compared with previous values, maimly for uniqueness.

All data keys are dynamic and are expected to be changed.

So creating the table for the data with specific columns is not a solution, as their title and number may vary.

We proposed to a client storing XML or json with dynamix data in the table and then deserialize it for analysis.

But eventually we left with the decision to append the new column to the table each week, and will it with as much data as we have and then compare the lists inside of columns.

Basically, it's a horizontal-growing table.
Deep inside I see that it's a bad idea, but unfortunately I don't have a strong argument about it. Could you please help me?

How bad is this design and which consequences we should expect with it?

-----------------------------------------------------------------------------
| PropertyName W1 | Property Value W1 | PropertyName W2 | Property Value W2 |
-----------------------------------------------------------------------------
| Title           | SomeTitle         | OtherProp       | PropValue         |
-----------------------------------------------------------------------------
| SomeProp        | SomeVal           | Title           | SomeUpdatedTitle  |
-----------------------------------------------------------------------------

Best Answer

If the values in one row are not related to each other, adding columns is pointless.

Writing values into the new columns requires rewriting entire rows, and reading a value requires reading the entire row (up to the desired column). So that schema becomes increasingly inefficient.

Put the week information into a separate column:

week  name       value
---------------------------------
  1   Title      SomeTitle
  1   SomeProp   SomeVal
  2   OtherProp  PropValue
  2   Title      SomeUpdatedTitle

With this schema, you do not have to pick specifics weeks to compare; you can also search for weeks where something changed, or compute distances between changes.

Related Question