In working with my client, we figured out that many of the objects in the
database have to have "notes" attached to them. Staff members have to be able
to record comments on exceptions, or quirks, or historical relevancies, and on
and on. Some notes need to be published in the catalog; some need to appear on
the public web site; some are just for staff.
My schema has been collecting multiple notes fields on table after table, and I
finally realized that there's a much better way to organize the dang things.
The following hare-brained scheme takes advantage of the fact that my primary
keys are not sequences, they are UUIDs.
I just need a single Notes table:
CREATE TABLE notes (
note_id uuid NOT NULL,
note text,
notecategory_ids _uuid,
reference_id uuid NOT NULL );
Ordinarily, 'reference_id' would be a foreign key to the row in another table
that this was a note about. But since I've got notes all over the database, I
can't just tell Postgres that it's a foreign key. reference_id is going to have
the primary key value of the row in the table it belongs to, of whatever table
that happens to be.
This will make it very difficult to start from a Note and figure out what entry
it belongs to, but that's not how it would ever normally be accessed anyway.
Instead, if I'm looking at a User record, or a Product record, or a Supplier
record, I can call up any notes for that record by just matching that record's
primary key to the Notes table's reference_id field.
This scheme seems both wonderfully elegant and fraught with hidden danger.
{grin} I will certainly need to add delete triggers on any table that a note
could be linked to, so that a deletion on that table will take any notes along
with it.
So, assuming nobody manages to talk me out of this idea in the first place, how
do I explain it to Sequel??
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sequel-talk?hl=en.
