cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Hugi Thordarson <h...@karlmenn.is>
Subject Compound PK/FK fun
Date Mon, 21 Oct 2019 11:18:56 GMT
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
Mime
View raw message