Thanx for thee replies. I'm still undecided but am leaning towards the
more "correct" approach of meaningless keys. I design the database and
write the software that access it so, if it doesn't work, I know who to
blame. :-)
Thanx again,
Garth
On 11/02/2012 08:40 PM, Aristedes Maniatis wrote:
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
