On Sep 1, 3:22 pm, Dave Howell <[email protected]> wrote:
> 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 is generally referred to as polymorphic associations. They
violate referential integrity and are a bad idea in most cases (IMO).
> 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.
If you are never accessing it that way, then it's definitely a bad
design (again IMO). Use a separate notes table for each main table.
> 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.
Using a separate notes table per main table is the better way to
handle things. It preserves referential integrity. Your solution is
only elegant in the sense that it reduces the number of tables. As
tables are not a scarce resource, it doesn't gain you much.
> So, assuming nobody manages to talk me out of this idea in the first place,
> how do I explain it to Sequel??
There's a sequel_polymorphic plugin, though it's a bit old and I'm not
sure if it still works (http://github.com/jackdempsey/
sequel_polymorphic). Even if it doesn't work, it shouldn't be hard to
bring it up to do. That's a guess, as I've never used it.
There are certain cases where polymorphic associations make some
sense. However, in general, for those situations, a relational
database is probably the wrong tool for the job.
Jeremy
--
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.
