# Modelling Unique Collections in Rails/SQL

**URL:** <https://www.ruby-forum.com/t/modelling-unique-collections-in-rails-sql/75826>\
**Category:** Ruby\
**Created:** [November 13, 2006, 6:51pm UTC](https://www.ruby-forum.com/t/modelling-unique-collections-in-rails-sql/75826 "2006-11-13T18:51:44Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![acgreen](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/3bc359/32.png) [@acgreen](https://www.ruby-forum.com/u/acgreen)\
**Post date:** [November 13, 2006, 6:51pm UTC](https://www.ruby-forum.com/t/modelling-unique-collections-in-rails-sql/75826/1 "2006-11-13T18:51:44Z")

</div>

I’m puzzelling over how to model _for want of a better description_  
unique collections in Rails and SQL

Heres my Domain Specific Example:

I start with…

class Band  
has\_many :line\_ups # ?  
end

class LineUp  
belongs\_to :band #?  
end

class Recording  
belongs\_to :line\_up # ?  
end

class Tour  
belongs\_to :line\_up # ?  
end

But I’m struggling to fully model the relationship between Bands,  
LineUps and People in Rails and SQL

LineUps are defined/distinct/unique from one another by the collection  
of People in them.

Any ideas ?

Tony

---

<div class="post-metadata">

**Author:** ![acgreen](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/3bc359/32.png) [@acgreen](https://www.ruby-forum.com/u/acgreen)\
**Post date:** [November 13, 2006, 10:56pm UTC](https://www.ruby-forum.com/t/modelling-unique-collections-in-rails-sql/75826/2 "2006-11-13T22:56:50Z")

</div>

removed\_email\_address@domain.invalid wrote:

> end
> 
> But I’m struggling to fully model the relationship between Bands,  
> LineUps and People in Rails and SQL
> 
> LineUps are defined/distinct/unique from one another by the collection  
> of People in them.

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 ?

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 V.

---

<div class="post-metadata">

**Author:** ![acgreen](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/3bc359/32.png) [@acgreen](https://www.ruby-forum.com/u/acgreen)\
**Post date:** [November 14, 2006, 9:25am UTC](https://www.ruby-forum.com/t/modelling-unique-collections-in-rails-sql/75826/3 "2006-11-14T09:25:54Z")

</div>

> > Any ideas ?Personally, I wouldn’t bother.

Obviously not the answer I was looking for.  
🙂

> 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. I

Unfortunately Line Ups don’t have start / end dates. Its more complex /  
simpler that that.  
LineUps change all the the time from gig to gig, recording to to  
recording so I’m thinking a LineUps should just be a unique collection  
of People tied to a band\_id

How can that be modelled in SQL ?

A unique composite field of people ids ?

Tony