Hi,

I develop an application that has many different and sometimes completely unrelated with eachother entities, for example it has Invoices, Customers, Orders, Problems, Payments etc. Each of the entities have their own Table and the database is mostly normalized.

I have a requirement to built an independent Notes system that can be attributed to all of the entities. In other words, each entity can have notes attached to it by the end user.

For this I've created a table named Notebook, which has:

id int(11)
title varchar(600)

and a Notes table which has:

id int(11)
notebook int(11)
date datetime
text text

and in all the other entity tables, I have a field Notebook int(11) which is a foreign key pointing to the notebook entry.

It works beautifully when I select, add, update and delete Notes on each entity, but there is a problem.

The problem is that there is no way I can select the related entity from the Notebook/Notes table. Because there is no relation in the Notebook table referencing back to the entity that "belongs" to.

Is there a proper way of implementing a Shared Table that holds common data and can be used to relate the data back and forth without relying on external program searching each and every table? I ask this because this is bad design and will lead to problems in the future (adding more tables needs to update the code for searching for example)?

Thank you in advance for your time and help.

There are two ways to handle this, each equally valid but serving different purposes.

From your description of your model, notes are a separate entity in and of themselves and are associated with many other kinds of entities. Normally when I see this in a model, it is because the note actually is intended to be attached to many different kinds of other entities and often to more than one entity. An example would be where there are multiple "orders" associated with a given "invoice" and a common "note" should be shared by some or all of them.

One way to handle this is exactly what you've modeled, where the PK for the note appears as a nullable FK in each of the entities that can reference it. This is what you've got now, and it is clean and simple for everything except the "reverse lookup" of the note to the entities that the note references.

A second way to handle this is to create a "many-to-many" linking table for each of the entities that can reference a single note. In my example this would mean an "orderNote" table that contains FKs for the order and for the note, and an "invoiceNote" table that contains the FKs for the invoice and for the note. This is more flexible and is often preferred to the simpler solution that you're using now.

The difference between the two models is subtle. It makes for a rousing discussion as data modelers "belly up to the bar" and discuss the Nth normal forms. There are a few practical advantages to each model because the two solutions are similar but not equivalent.

-PatP

I see.

This is what I thought myself, one extra referencing table for each of the entity tables that have notes as an alternative.

But the main problem applies to this approach too, as you see to make a "reverse lookup" selection from the Notebook table, I would still need to know the referencing table's name e.g. orderNotes before I can query it and still I need to execute multiple queries to exhaust all the referencing tables and relations.

I was thinking of a third approach, a bit more dirty I confess, but could make the job done:

In the Notebook table I could put an extra field that will carry the related ID from the entity that belongs to (as a FK) and a "table" field that will carry the name of the database table. In this way I will know with one query which the db table and the entity ID that the notebook/note relates to.

Ofcourse this still is bad design and can lead to problems but it's more close than the other approaches to what I try to accomplish.

What do you think?

And as a second question, can I use the title of the table (as varchar) returned from a subquery to subsequently select from it in the same query, or there is no way and only through a function/procedure can this be done?

I'm not clear on what you hope to accomplish by finding all of the disparate entities that a given note might relate to, so I can't offer you much insight.

Your application has to know how to deal with the data that its queries return... If your result set mixes multiple types of entities, the chances that the application can cope with that are very very small. You'd need separate application code to deal with queries that return orders than you would for queries that return addresses or employees. I can't see how a "universal query" that returned every entity that referenced a given note or collection of notes would benefit your application.

A very small number of SQL dialects can cope with using a result set column as a reference to another object (such as a table name). This is common in other database paradigms such as Pick and MUMPS, and is a key feature of some of the LISP based databases but it is very rare in SQL.

-PatP

You are right, even if I could find all the entities's ids I would still need to separate them and treat them differently in my program code as they will be different entities so there is no benefit in pursuing that.

I will stick with my original implementation and make the code in my program execute a different query/search for each entity.

Thank you for your valuable help and your time.

So why not have another field in the notes table itself? Thus
and do away with the notebook table?

Having a notebook table lends itself nicely to code reuse. You can create an object or a group of functions to manage "notes" and reusue that code throughout your application. Everywhere the user sees a note, they can recognize it and handle it consistantly. This reuse is almost always a huge gain for both the developer and the user.

-PatP