0

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.

Jaime Montoya
  • 6,915
  • 14
  • 67
  • 103
  • 1
    This might be better asked at the sister site, https://dba.stackexchange.com/ – Basil Bourque Jan 31 '19 at 17:36
  • Good suggestion @BasilBourque. I just asked the same question there: https://dba.stackexchange.com/questions/228589/how-do-columns-with-excessive-null-values-hurt-database-performance. – Jaime Montoya Jan 31 '19 at 17:39
  • Please don't cross-post. Also please use text, not images/links, for text, including tables & ERDs. – philipxy Jan 31 '19 at 19:26
  • @philipxy The StackOverflow editor does not provide in the GUI a way to build tables, so I built it in Libre Office and put the screenshot here. I can write the table in HTML but it was quicker to do it in the Libre Office spreadsheet and then put the image here. It was not my intention to cross-post, but BasilBourque suggested that this might be better asked at the sister site... – Jaime Montoya Jan 31 '19 at 21:30
  • 1
    [Type a table](https://meta.stackoverflow.com/q/285551/3404097) as text formatted in a code block, which uses a fixed-width font. When a question is about code, best is tabular formatted code that initializes the table as part of a [mcve]. Re cross posting, please delete all but one copy of a question. You can google/search stackexchange re cross posting (etc). – philipxy Feb 01 '19 at 20:27
  • 1
    How is this not an obvious faq, eg easily found by googling your title with site:stackoverflow.com, and what is specific to your case that isn't addressed in similar posts? – philipxy Feb 02 '19 at 23:27
  • @philipxy Off-topic. It looks like now the focus is about how I did not format my table using text in a code block. Or are you saying that my question has an obvious answer that I should have looked up on Google instead of asking it here? Do you know the answer and can you share it here? – Jaime Montoya Feb 04 '19 at 08:09
  • I am telling you some things that will help you get an answer & not get downvotes, here & on later questions. Please address them. Please read [ask] & the voting arrow mouseover texts. Here in particular re research. – philipxy Feb 05 '19 at 01:42
  • @philipxy Thank you for your advice for future questions. – Jaime Montoya Feb 05 '19 at 16:08

0 Answers0