I'm primarily a non-database developer, however I am familiar with normalization techniques.
A coworker was suggesting that it is correct for a table to store 'description' and 'notes' as a foreign key rather than in the table for performance reasons, given that some of the records will contain NULL (or empty string in this case), and presumably will be split across pages. This conversation occurred after we noticed that the integrity of the one-to-one table design was broken with a duplicate record. It seems to me that if the developer who designed the column put it in the source table, all of the code that handles the one-to-one integrity would be unnecessary (and would have not caused this bug)
Pros of putting Nvarchar in a table
- never have the risk of duplication
- easier to work with, easy queries
Cons of putting Nvarchar in a table
- after insertion, if the notes field grows, might cause a page split and worsened performance when querying the table.
Additional question: when putting notes in a seperate table, is the design closer to 1st normal form?
related, which seem to defend putting the strings in the source table instead of a FK:
https://stackoverflow.com/questions/9540738/nchar-vs-nvarchar-performance
Are relations slower than a big, inefficient table?
https://stackoverflow.com/questions/1412040/do-i-need-a-separate-table-for-nvarcharmax-descriptions