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. Mar 16 '20 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. Mar 16 '20 at 12:56
  • Added example of design – Sergey Mar 17 '20 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 Mar 17 '20 at 16:32

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, privacy policy and cookie policy

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