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
