Evil relationships

[Hugi Thordarson <hu...@karlmenn.is>]

Hi all.
I’m currently working with an old DB with Extremely Meaningful™ PKs. Here are three tables from the DB and the columns that form their primary keys.

— Company
company_number

— Customer
company_number
customer_number

— Invoice
company_number
customer_number
invoice_number

As you’ve probably already figured out, “Customer” is joined to “Company” on “company_number” and “Invoice” is joined to “Customer” on “company_number” and “customer_number”.

For the most part, this object graph works fine in Cayenne, but there’s one thing: In addition to the relationship to “Customer”, “Invoice" also has a relationship to “Company” (based on company_number, having this direct relationship is useful for reporting). But if I set the “customer” relationship on “Invoice”, the “company” relationship does not get set.

Is there some way for me to notify Cayenne that more relationships might be involved in a change to “Customer”

Cheers,
- hugi

Re: Evil relationships

[Hugi Thordarson <hu...@karlmenn.is>]

Yeah, that’s how it’s currently modeled, but using that relationship instead of the direct one requires an additional join (which means a performance hit for large datasets).

- hugi



> On 13. mar. 2016, at 20:20, Lon Varscsak <lo...@gmail.com> wrote:
> 
> I don’t have an answer for you.  But I work with a lot of meaningful PKs.
> :D  I would have a relationship from Customer back to Company.  Then in
> reporting it would be invoice.customer.comapny.
> 
> -Lon
> 
> On Sat, Mar 12, 2016 at 4:09 AM, Hugi Thordarson <hu...@karlmenn.is> wrote:
> 
>> Hi all.
>> I’m currently working with an old DB with Extremely Meaningful™ PKs. Here
>> are three tables from the DB and the columns that form their primary keys.
>> 
>> — Company
>> company_number
>> 
>> — Customer
>> company_number
>> customer_number
>> 
>> — Invoice
>> company_number
>> customer_number
>> invoice_number
>> 
>> As you’ve probably already figured out, “Customer” is joined to “Company”
>> on “company_number” and “Invoice” is joined to “Customer” on
>> “company_number” and “customer_number”.
>> 
>> For the most part, this object graph works fine in Cayenne, but there’s
>> one thing: In addition to the relationship to “Customer”, “Invoice" also
>> has a relationship to “Company” (based on company_number, having this
>> direct relationship is useful for reporting). But if I set the “customer”
>> relationship on “Invoice”, the “company” relationship does not get set.
>> 
>> Is there some way for me to notify Cayenne that more relationships might
>> be involved in a change to “Customer”
>> 
>> Cheers,
>> - hugi

Re: Evil relationships

[Lon Varscsak <lo...@gmail.com>]

I don’t have an answer for you.  But I work with a lot of meaningful PKs.
:D  I would have a relationship from Customer back to Company.  Then in
reporting it would be invoice.customer.comapny.

-Lon

On Sat, Mar 12, 2016 at 4:09 AM, Hugi Thordarson <hu...@karlmenn.is> wrote:

> Hi all.
> I’m currently working with an old DB with Extremely Meaningful™ PKs. Here
> are three tables from the DB and the columns that form their primary keys.
>
> — Company
> company_number
>
> — Customer
> company_number
> customer_number
>
> — Invoice
> company_number
> customer_number
> invoice_number
>
> As you’ve probably already figured out, “Customer” is joined to “Company”
> on “company_number” and “Invoice” is joined to “Customer” on
> “company_number” and “customer_number”.
>
> For the most part, this object graph works fine in Cayenne, but there’s
> one thing: In addition to the relationship to “Customer”, “Invoice" also
> has a relationship to “Company” (based on company_number, having this
> direct relationship is useful for reporting). But if I set the “customer”
> relationship on “Invoice”, the “company” relationship does not get set.
>
> Is there some way for me to notify Cayenne that more relationships might
> be involved in a change to “Customer”
>
> Cheers,
> - hugi