In the few instances where I go the other way, it's limited to 2If you can combine your queries with a union, your table layouts must be very similar if not identical.
or 3 tables, and I do separate joins combined with a UNION.
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...
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?
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.
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 thereNot necessarily. This can easily happen with concurrent transactions (see above).
which wasn't attached to anything. First of all, the only way that could
happen is if there was some really broken code,
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, whatConstraints don't prevent bugs (nothing does). They *do* prevent data corruption though, and ensure the consistency of your data.
would the harm be? Obviously it's a bug, but constraints don't prevent
bugs totally either.
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.
Right. What if somebody forgets to use that sequence?
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.
What if you load your database from a backup and forget to reinit the sequence?
If that was the case, you would not be able to combine them with a union, as you said you do...
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.
No, it would not. Application logic has nothing to do with your database schema.I'm not going to merge my Payments table with my Sponsors table. That would just be nuts.
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 haveIt 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)
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 myYou don't want either of that (abandon, or clutter) :-)
schema?
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.
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.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.
That's exactly what inheritance does (yes, with a single line).
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.
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.
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 :-)
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.
How about 15 years of experience? :-)
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?
Dima
---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
