[Catalyst] Re: Adding relationships to loaded CDBI models

[samwyse]

On 6/21/05, James Tolley <james at bitperfect.com> wrote:
>  > How do you explicitly define a might_have relationship in sql? :)
> 
> > I haven't gotten anything into code yet, but for a long time I've
> > thought about using "SELECT COUNT(*) FROM table" to infer similar
> > facts.  If you have 1,000 Person records and only 500 Spouse records,
> > then obviously "Person might have Spouse".  OTOH, if you have 1,000
> > Person records and 1,500 Offspring records, then it is less obvious
> > that "Person might have Offspring".
> 
> I agree with Perrin that it's relatively easy to provide might_have
> relationships, little or no inferring required. All you need is primary key
> info and foreign key info, both of which are available (in mysql InnoDB at
> least). Can we agree that where there is a single primary key column
> referring to the primary key of another table, then that is a might_have
> relationship? If so, let's patch Class::DBI::Loader::mysql. I will volunteer
> to write the code, including tests.
> 
> The other thing on my mind is has_many mapping relationships. Can we also
> agree that where there is a table with a two-field primary key in which each
> field refers to the primary key of another table, then that is a has_many
> mapping relationship? If so, let's write that as well. I'll be happy to do
> it, if there's a consensus.

I'm not so sure that I agree with your semantics.  The question is,
how many objects of a particular kind might a particular object map
to?  As I see it, the choices are 0, 1, (0..1), (0..n), and (1..n). 
The first case is trivial; '0' means no relationship exists.  '1' and
'(1..n)' are 'has_a' and 'has_many', respectively, and are usually
easy to pick out.  If object A (beer) has a reference to object B
(brewery), then we can deduce that each beer has a specific brewery,
and each brewery has many beers.

'(0..1)' and '(0..n)' are trickier.  If object A (employee) has a
reference to object B (spouse), and that reference can be NULL, then
each person "might have" a spouse.  That seems to cover the '(0..1)'
case.  '(0..n)' is generalization of the '(1..n)' case, wherein a
brewery doesn't produce any beer; it gets lumped in with 'has_many'
and we hope that there's no need to differentiate.