up vote 2 down vote favorite

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

share|improve this question
  • If I am not mistaken, it was back in SQL2000 only that there was a limit of 8060 bytes per row, right? So if you are using SQL2000, your co-worker might be correct in his suggestion. Since SQL2005, a row can span multiple pages. Since SQL2005, the engine stores data better, so imho, there'd be no point to split your nvarchar to a different table. – RoKa May 10 '13 at 13:46
  • The duplicate records arose because the notes table did not have uniqueness enforced where it should. That's a simple problem to fix, independently of whether it makes sense to horizontally partition the table at all. I'd say "don't" until and unless it causes a demonstrable performance problem. Start simple, get fancy only if you must. – Jon of All Trades May 10 '13 at 23:05
up vote 1 down vote accepted

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.

Possibly, depending on RDBMS, the exact data types, and what the performance goal is.

For SQL Server, which it sounds like you're using, you can use sp_tableoption with large value types out of row to keep a LOB (nvarchar(MAX) in this case) column in the table you want (logically), while pushing the bulk of the data storage onto separate pages (physically).

This will make accessing the LOB data slower, but speed up access to only the base data (probably moreso for scanning) as the tradeoff. Depending on the average LOB size, this setting may not make a significant difference. You will have to test this for your exact scenario to see if it improves your workload.

In any event, as long as you want to keep the one-to-one relationship, there's no reason to logically separate the values from the base table because there is an available way around the problem in SQL Server, assuming you're using the MAX type variant. (Note: if you're currently using a fixed-length field, you could switch to the MAX type and add a check constraint to limit the length.)

This conversation occurred after we noticed that the integrity of the one-to-one table design was broken with a duplicate record.

It seems like the design of these structures is smelly. Are you keeping a single table to store all of these fields, which are then referenced from the parent tables?

A better design would be to only separate the tables where there's an actual need (again, as I mentioned, this may not even be required) with a declarative one-to-one relationship, such as repeating the primary key of the parent table as the primary key in the LOB storage table.

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)

Quite possibly. The fewer moving parts there are in a system, the more likely it is to be reliable, probably with fewer bugs as a result of complexity.

share|improve this answer
up vote 0 down vote

If you have notes in a separate table, you can have multiple notes per thing; there's no need to be restricted to a 1:1 relationship (unless that is a requirement). For example:


Foo
---
  id
  (other fields)

Foo_notes
---------
  foo_id
  note_id

notes
-----
  note_id
  note_date_time
  note_priority
  note_author
  note_content

If you are really only interested in a 1:1 relationship, then keeping short notes in the same table as the thing that's being noted is probably not so bad. There might be some performance problems if your notes are very big, and it could be argued that if the great majority of your records will have a null value, you should split those that don't to a separate table.

share|improve this answer
  • Thanks. many would have a null value. would you consider nvarchar(1000) short? – user376456 May 10 '13 at 15:14
  • @FrustratedWithFormsDesign Sorry for resurrecting this, but how would you handle many Foo like tables wishing to use a Notes table? For example, Tables Foo, Bar, Foobar and Barfoo wish to use the same Note table. Would you really have a Many-to-Many intermediary table for every table wishing to use Note? – ahodder Jun 5 '13 at 16:11
  • 1
    @AedonEtLIRA: That would be one way to do it. It could lead to many link tables for these relationships. Another way would be to have one table that links any other table to Notes, and stores which table the other table is. So the record would contain the fields noteId, entityId, entityTypeId. entityTypeId would have to be translated in the application to refer to Foo, Bar, Foobar, etc... The problem with this method is I don't think there's any way to directly connect entityTypeId to a table and enforce it. – FrustratedWithFormsDesigner Jun 5 '13 at 17:09

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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