Hi Bruce,
I am in favor of the third option ( a separate autonumbered PK for the MtoM
table and you can put an unique index on both FK columns together.
Advantage: After having selected a row you easily can select and manipulate the
row by its own PK and you don't need to keep in mind to concatenate field every
time you have to perform an update or something else.
Also a lesson learned in history is never put any information into a PK, it's
just a number and keep it that way.
Tony
-----Original Message-----
From: [email protected] [mailto:[email protected]] On Behalf Of Bruce Chitiea
Sent: woensdag 13 juli 2011 17:31
To: RBASE-L Mailing List
Subject: [RBASE-L] - Many-2-Many Table Primary Keys: Recommendation?
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
2
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
