November 26, 2008 at 8:17 pm
Hi All
In our design, we have a Notes table, which can be linked to many other tables (Supplier, WorkItem, Quote and so on), which allows us to store notes against multiple objects in the database, while still keeping them centralised in one table.
My boss has proposed two designs for the table and asked for our opinions:
tblNotes
idNote
*various note related fields*
fkidContract
fkidSupplier
fkidWorkItem
fkidQuote
fkid(etc etc etc)
In this design, only one of the foreign keys will be filled in, with the others being null, since each note only relates to a single object.
The other design:
tblNotes
idNote
*various note related fields*
fkidOtherReferences
inRefType
In this design, we simply have one foreign key field, which will be used to link to many different tables. We also have the ref type so that we know which table is being linked to.
The second design is obviously a bit of a cheat, but simplifies the model so that we don't have heaps of null foreign key fields. I'm slightly in favour of the first option since it seems like the more correct design.
Which of these designs would be better? Can you suggest another design that would work better than either of these?
November 26, 2008 at 8:30 pm
dan (11/26/2008)
Hi AllIn our design, we have a Notes table, which can be linked to many other tables (Supplier, WorkItem, Quote and so on), which allows us to store notes against multiple objects in the database, while still keeping them centralised in one table.
My boss has proposed two designs for the table and asked for our opinions:
tblNotes
idNote
*various note related fields*
fkidContract
fkidSupplier
fkidWorkItem
fkidQuote
fkid(etc etc etc)
In this design, only one of the foreign keys will be filled in, with the others being null, since each note only relates to a single object.
The other design:
tblNotes
idNote
*various note related fields*
fkidOtherReferences
inRefType
In this design, we simply have one foreign key field, which will be used to link to many different tables. We also have the ref type so that we know which table is being linked to.
The second design is obviously a bit of a cheat, but simplifies the model so that we don't have heaps of null foreign key fields. I'm slightly in favour of the first option since it seems like the more correct design.
Which of these designs would be better? Can you suggest another design that would work better than either of these?
I'm going to go with the third option, and that is the one where each table (Supplier, WorkItem, Quote and so on) has its own Notes table. The reasoning discussed above regarding the table wouldn't even be necessary. In addition, this would be more inline with 3rd normal form design of a database.
November 26, 2008 at 9:21 pm
Hi Lynn
Thanks for the reply. What you're saying makes sense, but it seems slightly wasteful to have so many notes tables sitting around. We could have up to 10-20 different tables in the database, which will all be the same table that just links to a different parent table.
On the other hand, I suppose that having 10-20 fields in a table that are all null would be similarly wasteful.
Have any articles ever been written on this subject? I'm sure this problem has come up for many developers at some point.
November 26, 2008 at 9:42 pm
I am unaware of any articles that I could point you toward. Your best bet would be to look at some of the books on database design or data modeling. Those should be helpful in this area.
November 27, 2008 at 6:51 am
Let me chime in by seconding Lynn's opinion. I have worked on databases that have gone with option 2 and that is a fairly difficult design to work with, while Lynn's option is much simpler to work with and easier to understand. Also as Lynn points out his suggestion is 3rd normal form as every column relates to the key, while the other designs do not necessarily fit that.
Just my 2 cents worth.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 27, 2008 at 10:32 am
dan: The question to be answered is "what is the real value of having all of these notes attached to different things collected into the same table?" I think that you'll find that it is not what it first seems:
1) Efficiency/Performance: This does not actually help performance in any way, unless you will frequently have a need to gather many notes from different tables and that raises the question of how you would relate them together. And for any retrieval of notes associated with a single table (almost certainly the normal way that you will want them) individual tables will be more efficient.
2) Efficiency/Space: virtually no difference here.
3) Efficiency/Maintenance: This only comes up if the Notes tables requires some kind of individualized maintenance that other tables do not, otherwise they would be maintained the same way as every other table: en-mass with database wide operations and procs. The total number of tables is generally less important here than the total size, which should be the same (see #2 above).
4) Efficiency/SQL-Coding: Near-duplicate SQL stored procedures can be auto-generated without much difficulty. Slightly more work for individual tables over-all, but it is less error-prone and buggy, typically.
5) Efficiency/Client-Coding: Easily handled with inheritance and encapsulation: only slightly more work for individual tables.
One key to making (4) and (5) work best is to have some deterministic naming rule that can generate the individual Notes table's name based on the name of the parent table they are associated with. An example rule could be: "Add '_NOTES' on to the end of the parent table's name". Thus if the Contract table is called "CONTRACT" then the notes table would be "CONTRACT_NOTES", if the parent table is called "tblSUPPLIERS" then the notes table would be called "tblSUPPLIERS_NOTES" and so on.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 27, 2008 at 5:05 pm
Thanks for the advice everyone - it seems that there is overwhelming support for the individual tables design. I agree that it seems the best option and I'll put forward the case!
November 27, 2008 at 5:38 pm
Glad we could help.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 27, 2008 at 10:43 pm
Hi All
Just following up in case anyone was wondering what our final solution was.
Our notes table contains a few fields, such as note type, an optional file attachment and extra details, so we didn't want to duplicate this table too much. Instead, we came up with a 4th solution, which I believe is still good database design, but allows us to not replicate the table.
For each entity (WorkItems, Contracts, Suppliers and so on) we're going to make a bridging table between the table and the notes table. So we'll have WorkItemNoteLinks, ContractNoteLinks, SupplierNoteLinks. These bridging tables will simply contain the foreign keys from the corresponding entity and note. This allows us to reuse the notes table while still maintaining a good design. As a bonus, if we ever wanted to associate a note with two entities (for example, WorkItems and the Supplier that worked on it) we'll be able to do that as well.
Thanks again for the advice!
November 27, 2008 at 10:52 pm
Sounds like a good and flexible solution.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 28, 2008 at 2:51 am
5th Solution:
Add two tables for Notes:
Notes table
------------
NotesID int PK
NotesItem
------------
NotesItemID int PK
NotesID FK (to Notes table)
Memo
Etc
And add for each table (WorkItems, Contracts, Suppliers, etc) that must support Notes an additional column NotesID.
Now when a supplierID get's its first Notes then take the following actions:
1. create a new record in the Notes table
2. create a new record in the NotesItem table (and use the NotesID from step 1)
3. Update the Supplier.NotesID field with the value from step 1
Now you only have to add two tables. Also you can immdeiately see if a Supplier has Notes (supplier.NotesID <> NULL). If it has no Notes then supplier.NotesID = NULL
November 28, 2008 at 7:42 am
johanvanbragt (11/28/2008)
5th Solution:Add two tables for Notes:
I assume in this scheme that you really intend for the "Notes" table to be a "NotesHeader" table? If so, then you probably would want to incorporate some kind of Category indicator (or series of flags) in the notes header rows to allow for easy/efficient reverse look-ups.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 28, 2008 at 7:53 am
Yes, perhaps NotesHeader is a better name, since Notes gives you the idea that it contains the notes(text) itselves, which is not the case. And then the NotesItem table can be renamed to Notes.
In my opinion there is no need to add a Category indicator to this NotesHeader table, since you do not need to use the NotesHeader table at all in your queries:
SELECT S.SupplierName,
N.Memo
FROM Supplier as S
INNER JOIN Notes as N on N.NotesHeaderID = S.NotesHeaderID
November 28, 2008 at 8:10 am
Yes, but that assumes that you already know that the Notes you want to look at are Supplier Notes.
What if you need to go the other way? What if you need to find any Note that mentions the "Framus" project and it's associated parent(s)?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 28, 2008 at 8:31 am
I do not know if that is a necessary requirement. But when it is then you still can do it, but this query should be modified whenever another table also gets a reference to the NotesHeader table:
SELECT N.Memo,
CASE WHEN S.NotesHeaderID IS NOT NULL THEN 'Supplier:'+S.SupplierName
CASE WHEN P.NotesHeaderID IS NOT NULL THEN 'Project:'+P.ProjectName
CASE WHEN C.NotesHeaderID IS NOT NULL THEN 'Customer'+C.CustomerName
etc
ELSE 'Unknown'
END as BelongsTo
FROM Notes as N
LEFT JOIN Supplier as S on S.NotesHeaderID = N.NotesHeaderID
LEFT JOIN Project as P on P.NotesHeaderID = N.NotesHeaderID
LEFT JOIN Customer as C on C.NotesHeaderID = N.NotesHeaderID
etc
WHERE N.Memo like '%Framus%'
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply