Pb link between tables and primary key

[nianko]

Hi,
I started creating tables for a new financial Database. To simplify, I have a table "tbl_DEALS" that lists and describes a given security's characteristics (say, if it's a bond for instance, we would have [name of the borrower], [issue date], [type], [amount], [currency] and so on). I also want to create a field in that same table called "Syndicate" which would be a heading for a list of lenders under 1 specific security.
I therefore have an item Bond A in my table tbl_DEALS and under the Syndicate field, i will have a list of banks that are lending (bookrunners) to that bond issuer.
What type of relationship shall i create between those 2 tables (i also have a general tbl_BANKS that list all my universe of banks)?
Thanks in advance

 

[tehNellie]

It sounds like "Syndicate" is functionally a many-to-many relationship.

One deal can have many banks in its syndicate, and one bank can be a part of many deal syndicates.

Your deal table as described is not currently sufficiently normalised.

[Borrower] represents a relationship to a Customer (table), presumably, one borrower might have many deals?

[Type] represents a relationship to a "DealType" Table as you'll have a number of specific types (3 year bond, 5 year bond etc etc etc)

[Currency] Might represent a link to a currency Table, though you might argue that it could be a 3 character text field, "EUR", "USD", "GBP" etc etc but a table can give finer control over exactly what goes there and how you manage options around currency.

"Syndicate" Effectively becomes a Junction Table to handle the many to many relationship with [at least] two columns: DealID and BankID which might be a candidate for a compound primary key. i.e. one Bank can be part of many deal syndicates and one deal can have many banks, but one deal cannot relate to the same bank more than once.

 

[nianko]

Hi, thanks a lot for this answer. This is exactly it a many-to-many relationship. I have never used it although i know it's quite basic and fundamental. So from what you say, i should have a "Syndicate" Junction Table that sits between tbl_DEALS and tbl_BANKS which contains 2 keys (Deal_ID and Bank_ID). Is that correct? DO you know a good link to create a Junction Table?

Thanks

 

[tehNellie]

http://www.databasedev.co.uk/many_to_many_example.html - seems fairly comprehensive and is based on Access.

A junction table is just a table with two one-to-many relationships.

Create the Syndicate table
Add SyndicateID, autonumber, indexed no duplicates
DealID, Long Integer
BankID, Long Integer

Create a relationship from DealID to the DealID in the Deal table
Create a realtionship from the BankID to the BankID in the bank table.
ensure Referential Integrity is checked in both cases.

in both cases the "many" side of the relationship is the syndicate table.

If you then Highlight both DealID and BankID columns in the syndicate table and set them as the primary key.

SyndicateID might be irrelevant, but I like each table to have their own key/ID columns, at some point you might want the Syndicate table related elsewhere or have additional contextual information added to it at that point each table column having it's own inorganic ID might be useful.

If there is information specific to the syndicate itself (name, manager etc etc), that potentially requires a separate table, this is simply providing a junction between Deal and Bank tables (and any information specific to that particular Deal/Bank relationship) at the moment.