So, how can you possibly tell when looking at your note which entry it applies to?
Do you know of a better way to handle multitable references?
Sure. SET CONSTRAINTS DEFERRED; BEGIN; insert this insert that END;
That only handles single table references.
For example, I have a database with a "notes" table. This table is used to store annotations on ANY record within the database on ANY table. I use a single 64-bit sequence for every key within the database. In fact, in the GUI framework I ahve set up, in order to add notations to any screen, I can just add the following code:
note_html($object_id);
and it will insert code to be able to add/update/remove notes on that
object. So, my table NOTES has references to about 5 different tables so
far (it will probably grow with the application), all with the same
column.
You have your 64-bit id in the note, but how do you know which table to actually look for that id???
When you delete an object, how do you make sure, that the notes that refer to it get deleted too?
When you insert a note, how do you know the object it is referring to exists?
When you insert a new object, how can you be sure there is no object in another table with the same id?
The common way to do this kind of thing is (depending on the application, and particular object's properties) either to merge your five tables into one (possibly, adding an object_type column) or to split your notes table into five (one for each object table), and then make the notes reference the appropriate object.There are actually numerous uses of this - I once started writing a paper on building a generic database structure that could be reusable across multiple applications, but never had the time to finish it.
If you want to be really advanced, you might also want to look into the 'inheritance' approach... But I would not recommend that, because inheritance in sql is rather half-baked - the DDL code for such schema might look really elegant, but actually working with that database would be pain in the butt...
Finally, if for some obscure reason you have to have it assymetrical (one notes tabes referencing several different tables), you can always write your own trigger to ensure the referential integrity (like the FK does) against those several tables (you'll still need to have at least the object type in yoru notes table, so that your trigger knows which table to check against)...
The worst thing you can do in such situation is - just forget the constraints, and hope that your app will be able to enforce them on its own. It won't.
Dima
---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
