Indeed, the bottom line is this: Cayenne happily supports meaningful primary
keys. Just expose them in the modeler and make sure you have your own key
generation process in place.
I stand by my statement that meaningful keys are to be avoided whenever
possible. If you don't trust your database to store data accurately, losing
your FK connections is going to be least of your problems. However optimising
several milliseconds out of a query by avoiding a join is likely to be useful
in only very extreme use cases.
Ari
On 3/11/12 10:02am, Michael Gentry wrote:
Hi Garth,
If you have a legacy schema with meaningful PKs, it is fine to map
them. In general, especially when creating a new schema, meaningful
PKs are to be discouraged, but sometimes you don't have a choice.
mrg
On Fri, Nov 2, 2012 at 3:51 PM, Garth Keesler <[email protected]> wrote:
This issue remains a hot-button topic out there so here's my situation: I
have a list of abbreviated Vendor Names that haven't changed in ten years
altho it has been added to. That name is meaningful and is used in a variety
of places as a foreign key. It would also make a very nice PK since it is
permanent and guaranteed unique. One other advantage is that it is
meaningful wherever it is used so joins are frequently avoided. I've read
many Pro vs Con on meaningful PKs and FKs and one worry is how to recover
from a software error that jumbles these values. How to best reconnect? One
approach might be to unnormalize and copy the short name while still using a
meaningless FK but that is disaster waiting to happen.
I'm wide open to suggestion on this.
Thanx,
Garth
On 11/01/2012 05:44 PM, Aristedes Maniatis wrote:
On 2/11/12 6:35am, Garth Keesler wrote:
Can the PK as specified in the DBEntity be different than the PK actually
specified in the schema? I ask because I use DB-generated integer sequences
as the primary keys which are never used in the software. The software uses
an abbreviated form of the vendor name that is forced to be unique within
the table. Would it make more sense to specify that the shortened name is
the "real" PK in the definition of the DBEntity (which is hand-created, not
extracted from the schema)? FYI, this column is also used in may foreign
keys. Speaking of FKs, if I specify a relation in the DBEntity, should it
share the name of the FK specified in the schema or are they unrelated?
I suggest that you map Cayenne's concept of the PK to the real PK since
that is what you'll want to use for FK joins from other tables. Then just
create a normal field for your vendor code and add validation to keep it
unique. Making the vendor code the PK in any way is likely to cause lots of
pain down the track when one of your vendors changes names and wants a new
code.
One of the reasons Cayenne hides the PK from the Java entities by default
is that it is almost never a good idea to expose the PK to users of the
software.
Ari
--
-------------------------->
Aristedes Maniatis
GPG fingerprint CBFB 84B4 738D 4E87 5E5C 5EFA EF6A 7D2E 3E49 102A
