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
