| Subject | Re: [ib-support] Re: should foreign keys be surrogates like primary keys?? |
|---|---|
| Author | Doug Chamberlin |
| Post date | 2002-04-13T18:12:45Z |
At 04/13/2002 07:05 AM (Saturday), csswa wrote:
1) Field size. The effective difference between 1-byte and 4-byte fields
for code references is for the most part moot using today's hardware. Some
extreme situations may arise where is makes enough difference to be
concerned about but for developing a rule of thumb like you are I don't
think it should be considered.
2) Direct readability of the references. If you have people who like to
examine the records directly, having FK references which make some sense,
even if highly coded, can make a huge difference in people productivity.
3) Likilyhood of code values changing. Assess this likelihood carefully. If
it will not happen then short codes used directly as FK's make sense (see
#2, above). However, weigh this against the effort needed to modify the
code value. If updates cascade automatically with no performance impact of
the database then there is no issue. If you have to manage these changes
then you may have an unanticipated cost later on. Remember, even postal
codes and social security numbers CHANGE over time.
Myself, I'd go with all surrogate values for all FK references. It simply
makes sense to me. This after over 20 years of trying all worts of variations.
Most people do not think this through far enough and get burned in a
variety of ways, so having examined the issue ahead of time you are already
winning- even though it may not feel like it. Make an intentional decision,
document the reasons, and move on to the other fun stuff!
>uh, that subject line reflects my state of exhaustion after writingComment on some considerations which you mentioned:
>that last long post. Since foreign keys and primary keys are two
>sides of the same coin, "Should foreign keys be surrogates like
>primary keys..." doesn't make much sense. Helen is gonna spank me for
>that one! :-)
>
>What I mean is, in regard to the CODESET table, should I settle for
>consistently using the PK field as my key (i.e. 'meaningless' key) or
>use the CODE field (with SET lookup), or should I just use a
>combination where applicable...
1) Field size. The effective difference between 1-byte and 4-byte fields
for code references is for the most part moot using today's hardware. Some
extreme situations may arise where is makes enough difference to be
concerned about but for developing a rule of thumb like you are I don't
think it should be considered.
2) Direct readability of the references. If you have people who like to
examine the records directly, having FK references which make some sense,
even if highly coded, can make a huge difference in people productivity.
3) Likilyhood of code values changing. Assess this likelihood carefully. If
it will not happen then short codes used directly as FK's make sense (see
#2, above). However, weigh this against the effort needed to modify the
code value. If updates cascade automatically with no performance impact of
the database then there is no issue. If you have to manage these changes
then you may have an unanticipated cost later on. Remember, even postal
codes and social security numbers CHANGE over time.
Myself, I'd go with all surrogate values for all FK references. It simply
makes sense to me. This after over 20 years of trying all worts of variations.
Most people do not think this through far enough and get burned in a
variety of ways, so having examined the issue ahead of time you are already
winning- even though it may not feel like it. Make an intentional decision,
document the reasons, and move on to the other fun stuff!