-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On 11/17/07 01:21, Gregory Stark wrote:
> "Ron Johnson" <ron.l.johnson@cox.net> writes:
>
>> On 11/16/07 12:50, João Paulo Zavanela wrote:
>>> Hello,
>>>
>>> How many fields is recomended to create a primary key?
>>> I'm thinking to create one with 6 fields, is much?
>> The number of recommended fields is the *minimum* number required
>> for uniqueness. 1 or 6 or 24. Doesn't matter.
>
> Unless of course you care about every other table being 24x larger and slower
> due to having all these copies of the 24 fields. And of course unless you care
> about being able to handle the inevitable day when it turns out the 24 fields
> aren't unique and you need to consider adding a 25th column to the table *and
> every table referencing it* as well as changing every line of application code
> to use the new column.
What's got to be done has got to be done.
On one of our systems, the natural PK of an electronic road toll is:
ETC_ACCOUNT_ID INTEGER
FISCAL_PERIOD INTEGER
LANE_TX_ID BIGINT
TX_TYPE_ID CHAR(1)
TX_SUBTYPE_IND CHAR(1)
On another, it's:
ETC_ACCOUNT_ID INTEGER
FISCAL_PERIOD INTEGER
LANE_TX_ID BIGINT
DEVICE_NO CHAR(12) <<<< added column
TX_TYPE_ID CHAR(1)
TX_SUBTYPE_IND CHAR(1)
If the PK was synthetic and generated by the engine, then a (buggy)
app could insert duplicate tolls and the system wouldn't utter a
peep. But the customer sure would when he saw the duplicate entries.
Note the seemingly *synthetic* field LANE_TX_ID.
Records coming in from the lane are inserted into the T_LANE_TX
table which has the PK of LANE_TX_ID. However, that table also has
a "natural" unique index of LANE_ID, TX_DATE, TX_TIME, TX_SEQ_NUMBER
Likewise, T_LANE has the synthetic PK of LANE_ID, but it
back-stopped by a natural unique index PLAZA_ID, EXTERN_LANE_ID.
And... T_PLAZA has the PK of PLAZA_ID and is back-stopped by the
natural unique index AGENCY_ID, EXTERN_PLAZA_ID.
Breaking the chain, T_AGENCY only has the synthetic key AGENCY_ID.
But it only has 27 rows.
- --
Ron Johnson, Jr.
Jefferson LA USA
%SYSTEM-F-FISH, my hovercraft is full of eels
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
iD8DBQFHP701S9HxQb37XmcRAk1cAKCFnNraM2Z1s8M8j8sl8Gotxz1r0QCglEfJ
sUCabkDaZTQVc/kCyHGewhQ=
=b9ii
-----END PGP SIGNATURE-----
