FK to Multiple Tables

[sillysod]

Hi

(not sure if im posting in right place so apologies if its not)

I have multiple entities, for example
Customer
Suppler
Sales Invoice
Purchase Invoice
Currency

etc etc

I would like to be able to add a "Notes" record to ANY type of record

The Notes table would like this
NoteID - Int (PK)
NoteFK - Int
NoteFKType - Varchar(3)
NoteText - varchar(100)
NoteDate - Datetime

Where NoteFK is the PK of a customer or supplier etc and NoteFKType says what type of record the note is against

Now i realise that I cannot add a FK which references multiple tables without NoteFK needing to be present in all tables.

So how would you design the above ?
The note FK needs to be in any of the above tables

 

[jmeckley]

if you are using an ORM framework to map your domain to a database I would go this route. Nhibernate and Hibernate can handle this complexity.

if you are building this from scratch (without an ORM) The I would create a unique notes table for each entity.
Customer
CustomerNotes
Supplier
SupplierNotes
SalesInvoice
SalesInvoiceNotes

etc. This also allows you to customize the information collected about the notes. Say later on the SalesInvoice notes needs a new bit a information, like who entered the note.

a 3rd option: have a M:N table for each entity type to relate a note to it's parent.
Customer
Supplier
SalesInvoice
Notes
CustomerNotes
SupplierNotes
SalesInvoiceNotes

where CustomerNotes would be
CustomerId (pk)
NoteId (pk)
and the same would be repeated for SupplierNotes, SalesNotes etc.

This table structure would allow for a single note to be related to multiple entities. The business logic would ensure that a note is related to a single instance of an entity.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190