Compound PK/FK fun

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

Hi all.

I'm currently in the progress of cleaning up an older system and I have a bit of a conundrum that I would love some input on.

Almost all data in the DB belongs to/is related to a "Company" object. The PK of that company object forms part of a compound PK for the other objects.
For example; here "Customer" and "Invoice" are related to "Company" on the "company_number" attribute, "Invoice" has a related "Customer" joined on "company_number" and "customer_number".

Company
------------------------
* company_number (pk)

Customer
------------------------
* company_number (pk)
* customer_number (pk

Invoice
------------------------
* company_number (pk)
* invoice_number (pk)
* customer_number


Now, I've modeled all of this in Cayenne and it's been working very well for years for reporting purposes. However, we're now moving the "writing" part of the system to Cayenne as well, and that's caused a small problem.

For the most part it works very well. An object will never change companies once created, so if I set a "Customer" on an an "Invoice", it doesn't have any undesired side effects, it's essentially just setting the same company number again—no harm in that.
However, a problem occurs when I set an Invoice's customer to null. In that case, Cayenne will nullify both the invoice's customer_number AND company_number, which is obviously problematic.

I'm now wondering how I can solve this problem, i.e. how can I set an invoice's customer to "null" without blowing up the system.

I'm open to "dirty" solutions since this is a temporary situation. The Cayenne version of the system just has to work alongside an older system for a couple of months, but once that's been closed, I have full control of the DB and these tables will be getting their own shiny new unique PKs.

About the only thing I don't want to do is expose the FKs and/or handle relationships using manual fetching, since the relatiosnhips are already modeled and I have thousands of lines of logic that depend on the modeled relationships.

Any ideas?

Cheers,
- hugi

Re: Compound PK/FK fun

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

Hi Michael,
thanks for the input, but this does not work. Not even exposing the company_number attribute and setting it explicitly works, it's always set to null if it's involved in an FK change.

However, some experimentation seems to reveal that if multiple relationship changes touch the same FK column, the first change to set the field "wins". So if I set the Invoice's company (to be just the same company) before setting the customer, everything works as expected. Ugly as all hell, but I'm satisfied since it's a temporary workaround. At least until it blows up in my face somehow :).

In short, this is what's now in Invoice.java. I'll probably make it more generic and put it somewhere like setToOneTarget() on my DataObject superclass, to catch all cases when a relationship attempts to set "company_number" to null. If anyone can see something wrong with this (or think of a cleaner solution) I'm open to ideas.

@Override
public void setCustomer( Customer newCustomer ) {

	if( newCustomer == null ) {
		final Company actualCompany = company();
		setCompany( null ); // Required to register a change
		setCompany( actualCompany );
	}

	super.setCustomer( newCustomer );
}

Cheers,
- hugi


> On 21 Oct 2019, at 13:54, Michael Gentry <bl...@gmail.com> wrote:
> 
> Hi Hugi,
> 
> I have no idea if this will work, but try the following:
> 
> * in Cayenne Modeler, add companyNumber (company_number) as an attribute
> for Company.
> * Regenerate classes.
> * In Company.java, override the setCompanyNumber method to check for
> nulls.  If non-null, call the super; if null, ignore it.
> 
> 
> On Mon, Oct 21, 2019 at 7:19 AM Hugi Thordarson <hu...@karlmenn.is> wrote:
> 
>> Hi all.
>> 
>> I'm currently in the progress of cleaning up an older system and I have a
>> bit of a conundrum that I would love some input on.
>> 
>> Almost all data in the DB belongs to/is related to a "Company" object. The
>> PK of that company object forms part of a compound PK for the other objects.
>> For example; here "Customer" and "Invoice" are related to "Company" on the
>> "company_number" attribute, "Invoice" has a related "Customer" joined on
>> "company_number" and "customer_number".
>> 
>> Company
>> ------------------------
>> * company_number (pk)
>> 
>> Customer
>> ------------------------
>> * company_number (pk)
>> * customer_number (pk
>> 
>> Invoice
>> ------------------------
>> * company_number (pk)
>> * invoice_number (pk)
>> * customer_number
>> 
>> 
>> Now, I've modeled all of this in Cayenne and it's been working very well
>> for years for reporting purposes. However, we're now moving the "writing"
>> part of the system to Cayenne as well, and that's caused a small problem.
>> 
>> For the most part it works very well. An object will never change
>> companies once created, so if I set a "Customer" on an an "Invoice", it
>> doesn't have any undesired side effects, it's essentially just setting the
>> same company number again—no harm in that.
>> However, a problem occurs when I set an Invoice's customer to null. In
>> that case, Cayenne will nullify both the invoice's customer_number AND
>> company_number, which is obviously problematic.
>> 
>> I'm now wondering how I can solve this problem, i.e. how can I set an
>> invoice's customer to "null" without blowing up the system.
>> 
>> I'm open to "dirty" solutions since this is a temporary situation. The
>> Cayenne version of the system just has to work alongside an older system
>> for a couple of months, but once that's been closed, I have full control of
>> the DB and these tables will be getting their own shiny new unique PKs.
>> 
>> About the only thing I don't want to do is expose the FKs and/or handle
>> relationships using manual fetching, since the relatiosnhips are already
>> modeled and I have thousands of lines of logic that depend on the modeled
>> relationships.
>> 
>> Any ideas?
>> 
>> Cheers,
>> - hugi

Re: Compound PK/FK fun

[Michael Gentry <bl...@gmail.com>]

Hi Hugi,

I have no idea if this will work, but try the following:

* in Cayenne Modeler, add companyNumber (company_number) as an attribute
for Company.
* Regenerate classes.
* In Company.java, override the setCompanyNumber method to check for
nulls.  If non-null, call the super; if null, ignore it.


On Mon, Oct 21, 2019 at 7:19 AM Hugi Thordarson <hu...@karlmenn.is> wrote:

> Hi all.
>
> I'm currently in the progress of cleaning up an older system and I have a
> bit of a conundrum that I would love some input on.
>
> Almost all data in the DB belongs to/is related to a "Company" object. The
> PK of that company object forms part of a compound PK for the other objects.
> For example; here "Customer" and "Invoice" are related to "Company" on the
> "company_number" attribute, "Invoice" has a related "Customer" joined on
> "company_number" and "customer_number".
>
> Company
> ------------------------
> * company_number (pk)
>
> Customer
> ------------------------
> * company_number (pk)
> * customer_number (pk
>
> Invoice
> ------------------------
> * company_number (pk)
> * invoice_number (pk)
> * customer_number
>
>
> Now, I've modeled all of this in Cayenne and it's been working very well
> for years for reporting purposes. However, we're now moving the "writing"
> part of the system to Cayenne as well, and that's caused a small problem.
>
> For the most part it works very well. An object will never change
> companies once created, so if I set a "Customer" on an an "Invoice", it
> doesn't have any undesired side effects, it's essentially just setting the
> same company number again—no harm in that.
> However, a problem occurs when I set an Invoice's customer to null. In
> that case, Cayenne will nullify both the invoice's customer_number AND
> company_number, which is obviously problematic.
>
> I'm now wondering how I can solve this problem, i.e. how can I set an
> invoice's customer to "null" without blowing up the system.
>
> I'm open to "dirty" solutions since this is a temporary situation. The
> Cayenne version of the system just has to work alongside an older system
> for a couple of months, but once that's been closed, I have full control of
> the DB and these tables will be getting their own shiny new unique PKs.
>
> About the only thing I don't want to do is expose the FKs and/or handle
> relationships using manual fetching, since the relatiosnhips are already
> modeled and I have thousands of lines of logic that depend on the modeled
> relationships.
>
> Any ideas?
>
> Cheers,
> - hugi