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  |

  • Show a concrete example of the proposed design, and the queries you're going to use on it. How many rows and weeks will there be?
    – CL.
    Commented Mar 16, 2020 at 12:54
  • What exactly do you mean with "uniqueness"? Is it forbidden to use a key that is already being used in any other week?
    – CL.
    Commented Mar 16, 2020 at 12:56
  • Added example of design
    – Serhii
    Commented Mar 17, 2020 at 16:25
  • You don't really need to put "new column" in the same table as the "old column" in order to compare them.
    – mustaccio
    Commented Mar 17, 2020 at 16:32

1 Answer 1


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.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.