Re: CREATE TABLE with REFERENCE

>
>
>
>
>>>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.
>
>
So, how can you possibly tell when looking at your note which entry it
applies to?
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?

>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.
>
>
>
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.

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