-1

I have a legacy system that store comments in the database. I created a new system to replace the legacy system, that also stores comments in the database. For reasons that it would take too long to explain here, I must store the comments that come from the new system in another column, not in the same column where the comments from the legacy system are stored. I am trying to think if it would be healthier for the performance of my database to store the comments that come from the new system, in another table, instead of creating a new column in the same table because this would mean to have too many NULL values. It would mean that by design, every row would have a NULL value for sure. See the table below to better understand what I mean:

enter image description here

As I mentioned, with the new system, the column "Comments legacy system" will have NULL values from 2019 and on. I am trying to decide whether or not I should have the "Comments new system" column in the same table, which still forces to always have NULL values, or put "Comments new system" in a separate column.

I am trying not to hurt performance by having too many NULL values in columns that by the nature of the design, will always have NULL values. Those columns are not indices by the way, so that will not be a problem, but still I am concerned about hurting performance by having an excessive number of NULL values in my columns. Avoiding those excessive NULL values is one of the benefits of a normalized database by the way, so I assume excessive NULL values in columns are generally a bad idea and bad design. Thank you.

UPDATE 1:

Notice in the table, how if in 2019 I have 10 million rows, the "Comments legacy system" will be a column with 10 million NULL values in that column. This means that the table will have an excessive number of NULL values, by design. Similarly, if in 2018 or earlier I have 200 million rows, "Comments new system" will have 200 million rows with NULL values. Again, this will be a table with an excessive number of NULL values, by design. That is what I am concerned about and what makes me think if redesigning this, maybe with a new table to store both legacy and new system comments, to avoid columns with excessive NULL values.

1

Having nulls in the columns is normal. Performance won't be effected unless you are updating those null values in the columns any time later where there is a possibility of a page splits happening. If you have an index on one of these columns already and worried about your read performance, you can consider switching to using the filtered index for both your old and new columns.

Thanks

  • None of my columns is an INDEX, so no problems about that. – Jaime Montoya Jan 31 at 21:31

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.