Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
BruceT
Yak Posting Veteran
78 Posts |
 Posted - 2011-04-25 : 10:01:58
|
| Hi, Wondering what folks thoughts are on this. I'm working on refactoring an old database and I have a situation where there are several (around 20) tables that require free form case notes. Would you go with one Note table with source table and row identifier columns to get to a particular note, given that this note table could get very large, or would you go with an individual note table for each table that would require notes?Thanks |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-04-25 : 10:19:50
|
| It depends. I've dealt with multiple notes tables and wasn't too pleased with them, so my opinion would now favor a single notes table. This isn't always feasible though, if you have to maintain referential integrity, since the notes could belong to entities in multiple tables. |
 |
|
|
BruceT
Yak Posting Veteran
78 Posts |
Posted - 2011-04-25 : 10:31:11
|
| Thanks Rob.Yeah, I'm finding out if that's a requirement, but at this point it looks like one row in a table, one note for the row, so it's a straight one to one as far as rows and notes go. As long as I can assure the PK of the various tables that require notes are unique across the tables I should be ok. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-04-25 : 10:41:13
|
| If it's strictly one-to-one, you could put the notes column in that table. If it's a new table anyway, make sure to set up a separate filegroup for textimage data, and specify it as TEXTIMAGE ON [myTextFileGroup] when you create the table. Then you don't have to worry about unique keys across multiple tables or any other such crap. The additional benefit is that embedded notes don't clutter and fragment the other data. |
|
|
|
BruceT
Yak Posting Veteran
78 Posts |
Posted - 2011-04-25 : 11:26:13
|
| Good point, thanks Rob. If it turns out one to one that makes a lot of sense. |
|
|
|
|
|
|
|
|
Many tables or one table?