[Dbix-class] Dealing with relationships of non-order sensitive value pairs

[Darren Duncan]

At 3:19 AM -0500 2/5/08, Guillermo Roditi wrote:
>I am working on a DBIx::Class-backed DJabberd system with a Catalyst /
>Reaction front end and here's the basic explanation of my issue:
>
>Table vhosts has a store of all my DJabbed vhosts and they all have a
>numeric PK (i know the name could be a natural key, but i like numeric
>IDs sometimes)
>
>My vhost_bridges table has two fields vhost_1_id and vhost2_id which
>represent vhosts which are allowed to talk to each other. Because I am
>only dealing with two way relationships there is no difference between
>vhost1 and vhost2.

If there is really no difference between the roles of vhost1 and 
vhost2, then why not have each vhost_bridge contain 1 set-valued 
field having 2 elements, one for each vhost, rather than separate v1 
and v2 fields?  I think that more clearly illustrates your business 
rules, and arguably both the schema constraints and select queries 
would be conceptually (if not actually) simpler.

If you're using a DBMS that supports actual collection-valued fields, 
such as PostgreSQL, you could do what I said directly.  For other 
DBMSs, you would ungroup the set-valued field so you have 2 records 
in the main table per bridge, and then you add an extra column which 
holds some extra value in common for the 2 records in each bridge, to 
indicate they're together.  Either way your design benefits.

-- Darren Duncan