Re: CREATE TABLE with REFERENCE - Mailing list pgsql-general

> So, how can you possibly tell when looking at your note which entry it
> applies to?

That's easy - these are always referred from the table, never to the
table.  In the few instances where I go the other way, it's limited to 2
or 3 tables, and I do separate joins combined with a UNION.

> When you delete an object, how do you make sure, that the notes that
> refer to it get deleted too?

I only soft-delete items.

> When you insert a note, how do you know the object it is referring to
> exists?

Because it is always added from my note_html code, which only works for
working objects.  Let's say, theoretically, somehow some data got in there
which wasn't attached to anything.  First of all, the only way that could
happen is if there was some really broken code, but second of all, what
would the harm be?  Obviously it's a bug, but constraints don't prevent
bugs totally either.

> When you insert a new object, how can you be sure there is no object in
> another table with the same id?

We all use the same sequence.

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

Yes, but the tables have NOTHING to do with each other.  I'm not going to
merge my Payments table with my Sponsors table.  That would just be nuts.
Splitting the notes table would be pointless.  Why do it?  The way I have
it set up now, it takes _1 line of code_ to add note-taking capabilities
to my forms.  Why would I want to abandon that just to clutter up my
schema?  Then, if I want to enhance the note_html interface, I have to
modify the schema in 5 places (that's 5 places so far - as time goes on
this will likely increase to 10 or 15), and possibly have separate copies
of the note_html code.  That's craziness.  I can't think of one good
reason to do that.

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

It doesn't work for this approach. Inheritance is a single line - my
approach allows you to add "features" to objects at a whim.

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

Again, this would require modifying and testing that trigger every time I
want to add a new thing to take notes on.

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

You base this on.... what exactly?

Jon