From: David Vallner <david@...>
Date: 2006-11-14T06:56:17+09:00
Subject: Re: Modelling Unique Collections in Rails/SQL

--------------enig38345BC45FBE0438F55D3D88
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

email@acgreen.co.uk wrote:
> I'm puzzelling over how to model *for want of a better description*
> unique collections in Rails and SQL
>=20
> Heres my Domain Specific Example:
>=20
> I start with...
>=20
> class Band
>  has_many :line_ups     # ?
> end
>=20
> class LineUp
>    belongs_to :band  #?
> end
>=20
> class Recording
>  belongs_to :line_up      # ?
> end
>=20
> class Tour
>  belongs_to :line_up      # ?
> end
>=20
> But I'm struggling to fully model the relationship between Bands,
> LineUps and People in Rails and SQL
>=20
> LineUps are defined/distinct/unique from one another by the collection
> of People in them.
>=20

Ick. Somehow, I prefer SQL as a notation for relational data models
instead of Rails annotations - especially since I thought the whole
purpose of the magic and shenanigans was to only require them when
inferring the DB structure isn't possible.

This isn't a constraint a simple relational model can express, I'd say.
I personally don't think you specify it short of using an on update /
insert trigger and a very, very convoluted join to find out if there's a
full outer join of the sets of people of any pair of distinct lineups
that has no null values on either side.

(Maybe the above is possible in a more elegant way. Either way, I'd love
to see either the on insert trigger that verifies that constraint, or
the alternative solution - convoluted SQL is fun reading :P)

Either that, or you could use a validator, except doing this in-process
would do Horrible Things to performance unless the lineups are
read-mostly (preferrably never modified, only added), and you're caching
things accordingly, give-or-take prefetching the LineUps into the cache
on application startup.

(I'd also be mildly interested on just how you'd do that on Rails, but
that's veering into the scope of their mailing list too much and I fear
the redirect.)

> Any ideas ?
>=20

Personally, I wouldn't bother. It is entirely possible that a band
assumes the identical lineup twice in its history. I'd presume your
model also holds dates of when a lineup was formed / disbanded, it
should be sufficient to verify overlap of those dates. If you're using
separate Person records, the memory overhead to keep multiple
collections of those is completely negligible compared to what you store
in varchars anyway.

And to store dates of forming / disbanding of a lineup and enforce that
constraint, you'd have to compensate for the possibility of two
identical lineups for a band at different points in time by decomposing
LineUp into something like LineUpPeriod and PeopleSet, and then the
whole thing would go pear-shaped. Highly normalised relational models do
NOT map nicely to ActiveRecord and the API would turn baroque and
basically you'd be writing mostly raw SQL using Ruby as an alternate
syntax - something like RBatis, where your OO data model is decoupled
from the SQL structure would serve you better.

David Vallner


--------------enig38345BC45FBE0438F55D3D88
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.5 (MingW32)

iD8DBQFFWOnry6MhrS8astoRApGFAJ4yQqtGtclDUdMuR2eAdpjYLZlhBgCeP4XZ
snvnqXWeCiF68//IoyQvR68=
=GIG6
-----END PGP SIGNATURE-----

--------------enig38345BC45FBE0438F55D3D88--