Currency -> Currency Pair

[xspdr]

Hello there.

tblCurrency
PK, CurrencyName
1, USD
2, BTC
3, CNY
4, CND

tblTrade
FK?, CurrencyName & CurrencyName
1.2, USDBTC
3.1, CNYUSD

Is it possible to have two PK (from the same column) into one FK?
MS Access 2013

Thank you

 

[namliam]

yes possible, should you do it HEEEELLLLLLL NO !!!

Simply make 2 seperate columns be the combined PK instead.

 

[xspdr]

Thank you!

Should I make 2 columns on the tblTrade or should I make a third Cross Table?

tblCurrencyPair
PK, Currency1, Currency2
1, USD, BTC
2, USD, CND
3, USD, CNY

If you believe I should do this Cross Table to generate the PK,
What is the right way to do it? To auto generate new PKs as new currencys are added to the tblCurrency

thank you ;p

 

[namliam]

If you want/need a new PK for the combination, then sure...

PK FK1 FK2
1  1   2
2  1   4
3  1   3
etc..

Otherwize you can skip the PK for this table and just make FK1 and FK2 the combined key to prevent Dups from occuring

 

[xspdr]

Thank you for replying.

If there is a Composite Key of:

FK1, FK2
1, 21
12, 1

Would this mean I have a duplicate Composite Key of 121,
or Access would identify them as two different Composite Keys? (1,21) and (12,1)?

 

[namliam]

different composit keys (offcourse), it is about the combination of the fields.... 12 and 1 vs 1 and 21, it will never be about 121 that is unless you try and force it into one field as one key