On Feb 2, 2004, at 2:13 AM, Steve Wells wrote:
> Hey Andrus,
>
> Excellent that works. I guess I was still trying to link on the old
> natural-key columns instead of using generated ones...still getting my
> head around this concept. I would have actually thought I could "join"
> on any arbitrary columns?
>
> Is there a good reference somewhere explaining the benefits of
> generated
> vs meaningful/natural keys?
>
> Thanks for the speedy and very helpful response.
>
> Steve
Hi Steve,
SQL definitely allows to join two arbitrary columns. Strictly speaking,
the fact that Cayenne blows on this type of join should probably be
treated as a bug. Actually the reason why it didn't work for you was
that the target of to-one wasn't declared as PK (we have a similar
reported issue:
http://objectstyle.org/jira/secure/ViewIssue.jspa?key=CAY-41), and not
because it wasn't a generated integer.
So using generated keys is normally a matter of design consistency. My
rule of thumb is to use generated keys whenever possible, with two
exceptions:
1. Reports and data mining: Reports require ad hoc joining of data in
many unpredictable ways. You design DB schema for your business needs,
but you often design reports for whatever existing schema is already
there. Also reports are read-only, that makes things easier.
2. Lookup or configuration tables: Sometimes it seems appropriate to
relax the rules and define a meaningful PK in such tables as US_STATE,
ACCOUNT_TYPE, etc. Usually such tables are: (a) small, (b) do not grow
substantially over time, (c) data is populated by internal users.
Adding to Michael's Social Security Number example, when I was doing
consulting for a big pension fund, I heard horror stories of two people
being assigned the same SSN. So much for its uniqueness.
Andrus
This archive was generated by hypermail 2.0.0 : Mon Feb 02 2004 - 11:40:19 EST