Re: CREATE TABLE with REFERENCE

Jonathan Bartlett wrote:

>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.
>
>
If you can combine your queries with a union, your table layouts must be
very similar if not identical.
Why not put everything into the same table then, and just have an FK
between that table and the notes?

>
>
>>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.
>
>
What do you mean by soft-delete?
Leaving orphaned notes behind? Bad idea...

>
>
>>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.
>
How can you be sure? What if you get hit by a bus, and another admin,
who is not as knowledgeable as you are goes to the db, and runs an
insert with plain sql?
What if, while one connection runs your 'note_html' code, another one
deletes an object you are annotating?

> 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,
>
Not necessarily. This can easily happen with concurrent transactions
(see above).
Also, even if it was indeed only possible because of a broken code, you
are not saying that your code is bug-free, are you?
Even, if it was, postgres is not, and your filesystem and OS are not
either. If the database crashes in the middle of your insert, you'll end
up having inconsistent data.

>but second of all, what
>would the harm be? Obviously it's a bug, but constraints don't prevent
>bugs totally either.
>
>
Constraints don't prevent bugs (nothing does). They *do* prevent data
corruption though, and ensure the consistency of your data.

If I understand your statement ('what would the harm be') correctly, and
you just don't care about your data consistency, then, I guess, you are
rigfht - you don't need any constraints... but, in that case, I don't
think you need a transactional database to begin with. If all you want
from the database is being able to run queries, you are better off
running grep on a bunch of text files, or with some light-weight sql
tool, like mysql or sqllight - either of those will perfrom a lot
better, because they do not bother with the overhead of having to care
about your data consistency, and concurrent access.

>
>
>>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.
>
Right. What if somebody forgets to use that sequence?
What if you load your database from a backup and forget to reinit the
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.
>
If that was the case, you would not be able to combine them with a
union, as you said you do...

>I'm not going to
>merge my Payments table with my Sponsors table. That would just be nuts.
>
No, it would not. Application logic has nothing to do with your database
schema.
You need to design the schema to ensure effectiveness and reliability.

Then, you design your application on top of it, that handles the
business logic.
From the database perspective, there is no difference between payments
and sponsors, as long as both have the same (or similar) sets of attributes.
Iterpreting those attributes is not database's job.

>Splitting the notes table would be pointless. Why do it?
>
Because that would make it possible to use the constraints.

Also, if one adopts your earlier point, it can also be argued, that it
is equally 'nuts' to have notes about Payments stored together with
notes about Sponsors.
Those notes have just as much to do with each other as the objects they
annotate. :-)
If you insist that Payments must be separate from Sponsors, the same
exact argument should be applied to their respective notes

>The way I have
>it set up now, it takes _1 line of code_ to add note-taking capabilities
>to my forms.
>
It would *still* be one line of code with either of the approaches I
suggested. Your code doesn't really have to be affected at all
(although, I think, it would really benefit from adding the object_type
argument to your note_html() function, but even that is not necessary)

>Why would I want to abandon that just to clutter up my
>schema?
>
You don't want either of that (abandon, or clutter) :-)
You want that same one line of code, working against the properly
designed and normalized sql schema, that lets you rely on the database
top ensure your data consistency and access efficiency.

>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.
>
Not at all. If you give up your idea about splitting your payments from
your sponsors for example, you won't need to modify your schema *at all*
if you need to add another object type, or another kind of note, or
whatever - all you'd need to do would be to implement the new
application logic in your application, where it belongs, and be done
with it. No need to even touch your schema at all.

>
>
>
>>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.
>
That's exactly what inheritance does (yes, with a single line).
The particular implementations of inheritance in sql have their problems
(as I mentioned earlier), that make me really reluctant from using it,
but being able to add features to your objects, with a single line of
code isn't one of them - to the contrary, it's a *huge* benefit.

The actual problem, in my perspective, is that it kinda encourages you
to use that (inhernetly wrong approach) of treating database tables as
"objects", and columns as "features", and attempt implement your
application logic in sql, which is asking for trouble.

>
>
>
>>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.
>
Sure, if you implement in such way. But not if you give it some thought
in advance, and come up with an implementation that would be generic
enough not to care about your application-specific differences between
sponsors and payments :-)

>
>
>
>>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?
>
>
How about 15 years of experience? :-)

Dima