Are records bound to any table?

Question

Hi, I'm struggling a bit with this and can use some ideas ...

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

etc.

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

Note table will like

NoteID Int (PK) NoteFK Int NoteFKType Varchar(3) NoteText varchar(100) NoteDate Datetime 

Where NoteFK is the client or vendor’s PC, etc., and NoteFKType says what type of note the note is against

Now I understand that I cannot add an FK that refers to several tables without NoteFK that should be present in all tables.

So how would you work out above? Note FK must be in any of the above tables.

Cheers, Daniel

Answer 1

You must accept a constraint that you cannot teach the database about this foreign key constraint. Thus, you have to do without integrity checks (and cascading deletes).

Your design is fine. It is easily extensible for additional tables, you can have several notes for each object, and the target tables do not even need to know about the functions of notes.

The advantage associated with this design using a separate note table in the entity table is that you can easily run queries on all notes, such as “most recent notes” or “all notes created by this user”.

As for the argument of this table, which is getting too large, dividing it into five tables will reduce the table to about a fifth of its size, but it will not make any difference for index-based access. Databases are built to handle large tables (if they are properly indexed).

Answer 2

I think your design is fine if you can accept the fact that the db system will not check if the note refers to an existing object in another table or not. This is the only design that I can come up with that does not require duplication and scales to more tables.

How you developed it, when you add another type of entity for which you want to have notes, you do not have to change your model. In addition, you do not need to include additional columns in an existing model or additional tables.

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

Answer 3

I would think twice before doing what you offer. In the short term, this may seem simple and elegant, but if you are really interested in the integrity and performance of the data, it is possible to have separate note tables for each parent table. Over the years, I have approached this problem using solutions found in other answers (triggers, GUIDs, etc.). I came to the conclusion that the added complexity and performance loss are not worth it. With separate note tables for each parent table, with corresponding foreign key constraints, search queries and joins will be quick and easy. When combining related items into a single table, the join syntax becomes ugly, and the note table will be huge and slow.

Answer 4

I agree with Michael McLoskey, to some extent.

The question in my opinion is: what is the technical cost of having multiple note tables?

In my opinion, it is preferable to consolidate the same functionality in one table. This makes reporting and other further development easier. Not to mention that the list of tables is smaller and easier to manage.

This is a balancing act, you need to try to predetermine the benefits and costs of doing something like that. My personal preference is the referential integrity of the database. The application of integrity management should, in my opinion, be limited to business logic. The database should ensure the constancy and reliability of the data ...

To 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 this example, the final insert statement will fail.

Answer 5

You can get FK referential integrity at the cost of 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 need a constraint to make sure that you have only one of the columns set.

Or maybe not, you might want the note to be related to both the client and the product. To you.

For this design, you will need to add a new column in Notes if you want to add another link table.

Answer 6

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

This does not help data integrity. But this makes it easy for M-to-N to relate to any number of tables, and it eliminates the need to define a NoteFKType column in a Notes table.

Answer 7

You can easily implement a multi-foreign key with triggers. Triggers will provide you with a very flexible mechanism, and you can perform any integrity checks you want.

Answer 8

Why don't you do it the other way around and have a foreign key in other tables (Customer, Supplier, etc.) for NotesID. So you have one to one.