My personal practice is to use a PK on every table that is system
assigned, and I never worry about gaps in the sequence. The advantage to
the random approach is that with this approach the PK contains no data
or any item resembling data. This approach makes things a bit more
normal that the other approaches.
The only exception that I can recall that uses data as a key is price
lists, where the PK is the column PriceDate and CASCADE UPDATE is on the
header table. If someone enters a price list and then realizes that they
have done it all in 1984, they can change the price date and the whole
set of things will be updated.
I have an application under development that has a many to many table
for grouping people into teams. In this table I use a UNIQUE constraint
on the grounds that one team captain cannot have more than one member
#nnn, but the team member may belong to more than one team over the
years. R:Base treats a UNIQUE constraint like a PK. Because the only
point of the table is to link people into groups, I did not create a
separate PK. I may regret this later.
My US $ 0.0088 (exchange I got buying US $ yesterday)
Albert
On 13/07/2011 9:31 AM, Bruce Chitiea wrote:
All:
The Crawl-Walk-Run Department would surely appreciate your wisdom and
experience here.
Have you any recommendation for crafting PKs for many-to-many (M2M)
tables?
There's the compound approach; which seems to have the advantage of
being able to identify records within select statements directly by
their FK values:
Parent Table: TA
PK | TAPK
Parent Table: TB
PK | TBPK
M2M Table: TB2TA
PK | FK - TAPK
PK | FK - TBPK
Or the make-one-up approach; which seems to have the dual advantages of
allowing auto-numbering while providing a single PK value for
'downstream' coding.
M2M Table: TB2TA
PK | TB2TAPK
FK | TAPK
FK | TBPK
Or is there a viable third approach, like 'concatenating' TAPK and TBPK
into one value within the M2M table: TAPK-TBPK; which on the surface
looks like it might check all the advantage boxes?
Thanks in advance.
bruce chitiea
safesectors inc
--- RBASE-L
=======================3D=======================3
D=
TO POST A MESSAGE TO ALL MEMBERS:
Send a plain text email to [email protected]
(Don't use any of these words as your Subject:
INTRO, SUBSCRIBE, UNSUBSCRIBE, SEARCH,
REMOVE, SUSPEND, RESUME, DIGEST, RESEND, HELP)
=======================3D=======================3
D=
TO SEE MESSAGE POSTING GUIDELINES:
Send a plain text email to [email protected]
In the message SUBJECT, put just one word: INTRO
=======================3D=======================3
D=
TO UNSUBSCRIBE:
Send a plain text email to [email protected]
In the message SUBJECT, put just one word: UNSUBSCRIBE
=======================3D=======================3
D=
TO SEARCH ARCHIVES:
Send a plain text email to [email protected]
In the message SUBJECT, put just one word: SEARCH-n
(where n is the number of days). In the message body,
place any
text to search for.
=======================3D=======================3
D=
