Records linked to any table?

Question

Hi Im struggling a bit with this and could use some ideas...

Say my database has the following tables ; Customers Supplers SalesInvoices PurchaseInvoices Currencies

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

Cheers, Daniel

Answer 1

Why dont you do it the other way around and have a foreign key in other tables (Customer, Supplier etc etc) to NotesID. This way you have one to one mapping.

Answer 2

You could add a GUID field to the Customers, Suppliers, etc. tables. Then in the Notes table, change the foreign key to reference that GUID.

This does not help for data integrity. But it makes M-to-N relationships easily possible to any number of tables and it saves you from having to define a NoteFKType column in the Notes table.

Answer 3

I think your design is ok, if you can accept the fact, that the db system will not check whether a note is referencing an existing entity in other table or not. It's the only design I can think of that doesn't require duplication and is scalable to more tables.

The way you designed it, when you add another entity type that you'd like to have notes for, you won't have to change your model. Also, you don't have to include any additional columns in your existing model, or additional tables.

To ensure data integrity, you can create set of triggers or some software solution that will clean notes table once in a while.

Answer 4

You have to accept the limitation that you cannot teach the database about this foreign key constraint. So you will have to do without the integrity checking (and cascading deletes).

Your design is fine. It is easily extensible to extra tables, you can have multiple notes per entity, and the target tables do not even need to be aware of the notes feature.

An advantage that this design has over using a separate notes table per entity table is that you can easily run queries across all notes, for example "most recent notes", or "all notes created by a given user".

As for the argument of that table growing too big, splitting it into say five table will shrink the table to about a fifth of its size, but this will not make any difference for index-based access. Databases are built to handle big tables (as long as they are properly indexed).

Answer 5

You can easily implement "multi"-foreign key with triggers. Triggers will give you very flexible mechanism and you can do any integrity checks you wish.

Answer 6

I would think twice before doing what you suggest. It might seem simple and elegant in the short term, but if you are truly interested in data integrity and performance, then having separate notes tables for each parent table is the way to go. Over the years, I've approached this problem using the solutions found in the other answers (triggers, GUIDs, etc.). I've come to the conclusion that the added complexity and loss of performance isn't worth it. By having separate note tables for each parent table, with an appropriate foreign key constraints, lookups and joins will be simple and fast. When combining the related items into one table, join syntax becomes ugly and your notes table will grow to be huge and slow.

Answer 7

I agree with Michael McLosky, to a degree.

The question in my mind is: What is the technical cost of having multiple notes tables?

In my mind, it Is preferable to consolidate the same functionality into a single table. It aso makes reporting and other further development simpler. Not to mention keeping the list of tables smaller and easier to manage.

It's a balancing act, you need to try to predetermine both the benefits And the costs of doing something like this. My -personal- preference is database referential integrity. Application management of integrity should, in my opinion, be limitted ot business logic. The database should ensure the data is always consistent and valid...

To actually answer your question...

The option I would use is a check constraint using a User Defined Function to check the values. This works in M$ SQL Server...

CREATE TABLE Test_Table_1 (id INT IDENTITY(1,1), val INT)
GO
CREATE TABLE Test_Table_2 (id INT IDENTITY(1,1), val INT)
GO
CREATE TABLE Test_Table_3 (fk_id INT, table_name VARCHAR(64))
GO

CREATE FUNCTION id_exists (@id INT, @table_name VARCHAR(64))
RETURNS INT
AS
BEGIN
    IF (@table_name = 'Test_Table_1')
     IF EXISTS(SELECT * FROM Test_Table_1 WHERE id = @id)
      RETURN 1
    ELSE
    IF (@table_name = 'Test_Table_2')
     IF EXISTS(SELECT * FROM Test_Table_2 WHERE id = @id)
      RETURN 1

    RETURN 0
END
GO

ALTER TABLE Test_Table_3 WITH CHECK ADD CONSTRAINT
    CK_Test_Table_3 CHECK ((dbo.id_exists(fk_id,table_name)=(1)))
GO
ALTER TABLE [dbo].[Test_Table_3] CHECK CONSTRAINT [CK_Test_Table_3]
GO

INSERT INTO Test_Table_1 SELECT 1
GO
INSERT INTO Test_Table_1 SELECT 2
GO
INSERT INTO Test_Table_1 SELECT 3
GO
INSERT INTO Test_Table_2 SELECT 1
GO
INSERT INTO Test_Table_2 SELECT 2
GO
INSERT INTO Test_Table_3 SELECT 3, 'Test_Table_1'
GO
INSERT INTO Test_Table_3 SELECT 3, 'Test_Table_2'
GO

In that example, the final insert statement would fail.

Answer 8

You can get the FK referential integrity, at the costing of having one column in the notes table for each other table.

create table Notes (
    id int PRIMARY KEY,
    note varchar (whatever),
    customer_id int NULL REFERENCES Customer (id),
    product_id int NULL REFERENCES Product (id)
)

Then you'll need a constraint to make sure that you have only one of the columns set.

Or maybe not, maybe you might want a note to be able to be associated with both a customer and a product. Up to you.

This design would require adding a new column to Notes if you want to add another referencing table.