Table Advice For Storing Notes From Related Tables 1

[Auguy]

I have a master table and a detail table with the normal number of detail records for any one master record is about 3-5 records. Together there are about 600K records and growing at about 75K per year. There are two note fields for each master and each detail record. I'm moving the notes to a separate table and was wondering if there are any reasons not to put all of the notes into one table rather than a notes table for the master and detail? I'm planning on increasing the size of the notes to varchar(8000).

Auguy
Sylvania/Toledo Ohio

 

[Olaf Doschke]

>if there are any reasons not to put all of the notes into one table rather than a notes table for the master and detail?

What answer would you expect? It depends.

What reason do you have putting the same note fields into parent and child table in your current database design. There were reasons for that, weren't there? Notes for parent/head records have a different meaning than notes for child records, perhaps?

The discussion would perhaps be more important, if it was "real" data and not "just notes".

But again: It depends.

If you're asking, then there most probably are reasons to keep the notes separately. Technically you know to which table a note would belong, because a reference is coming from it. Technically you introduce the "risc", a note may be referenced twice from both parent and child record. You also risc you have notes referenced from several records, because they are the same notes right now. Modifying them then would change that note for all records referencing them. But that's true for both scenarios with 2 separate notes tables or just one.

Why not make all the involved fields in your current design NULLable, that'll already solve storage needs, if notes are only used sparsely. No other changes needed, no query changes, no code changes. Because overall I guess, you're introducing more cost than benefit in terms of work done for no new feature or benefit.

Bye, Olaf.

 

[Auguy]

Thanks Olaf. Let me explain a little further. The notes fields in the master and detail tables are all separate fields. They do not contain or point to the same notes field, even for the detail records. I'm moving the tables from SQL 2000 where I had the notes contained within the 8K record limit, to SQL 2012 (I hope) and expanding the size of the notes to possibly 8K characters each. I was thinking that having all of the notes in one table might make it easier to control and code my update transaction. Each notes record would have other fields identifying the master or detail record it belongs to as well as which notes field within the master or detail record.

Auguy
Sylvania/Toledo Ohio

 

[Olaf Doschke]

>Each notes record would have other fields identifying the master or detail record it belongs to as well as which notes field within the master or detail record.

If you would create a notes table, you would not let notes point to records they belong to, you would let records point to notes belonging to them, wouldn't you?

Because how would you define referential integrity, if the same foreign key of a notes tables could point to two different tables?

BEFORE
parenttable
ID, other fields, note1, note2
detailtable
ID, other fields, note1, note2

AFTER
parenttable
ID, other fields, notesID
detailtable
ID, other fields, notesID
notestable
ID, note1, note2

or

parenttable
ID, other fields, note1ID, note2ID
detailtable
ID, other fields, note1ID, note2ID
notestable
ID, note

But I would rather suggest:

parenttable
ID, other fields, note1 varchar(MAX) NULL, note2 varchar(MAX) NULL
detailtable
ID, other fields, note1 varchar(MAX) NULL, note2 varchar(MAX) NULL

It has one big advantage: Nothing needs to change, you still save memory, if many notes are not filled at all.

Bye, Olaf.

 

[Auguy]

Yes, you are correct, that was my poor attempt to describe the links between the tables. I like your last suggestion. I think I will still put a limit (varchar(8000)) on the notes size to prevent the users from getting carried away. That should be plenty for this app.

Auguy
Sylvania/Toledo Ohio

 

[Olaf Doschke]

Or do you have note1 and note2 because SQL2000 didn't have varchar(MAX) and you want to combine them to note = note1+note2?

Bye, Olaf.

 

[Auguy]

In one case yes, but the others are things like delivery notes vs internal notes we don't want the customer to see.

Auguy
Sylvania/Toledo Ohio