junction table questions
-
I have a many-to-many relationship I am trying to model in a fund accounting application.
Each fund has 5 accounts associated with it. Each account can be associated with many funds.
I am modeling the relationship with the following tables (not all fields are shown!):
1. Funds
Fund_ID int PK
Fund_Number varchar(10)
2. Accounts
Acct_ID int PK
Account_Number
3. FundsAccounts
FundsAccounts_ID int PK
Fund_ID int
Acct_ID int
Role_Description varchar(20)
The Role_Descrition field in FundsAccounts holds the name of the role the account plays for the fund
(i.e. AP_Cash_Acct, Due_Other_Funds_Acct etc.).
The five roles are the same for each fund. The same account can play more than one role for a fund.
That is why I have a FundAccounts_ID Primary Key instead of a compound key of Fund_ID and Acct_ID in the junction table.
Some questions:
1. Is this an efficient way to model this? Is it an issue that the 5 role_descritions wil be repeated for each fund
in the junction table?
2. Any suggestions on how to write a SELECT so that I end up witht the follwing fields?:
Fund,
Role1 Acct,
Role2 Acct,
Role3 Acct,
Role4 Acct,
Role5 Acct
Thank you for any help!
jmatt
-
1. Looks ok to me
2.
select
Fund_Number
, Coalesce( min( case when Role_Description = 'AP_Cash_Acct' then Account_Number end),'') as Role1_Acct
, Coalesce( min( case when Role_Description = 'Due_Other_Funds_Acct' then Account_Number end),'') as Role2_Acct
, Coalesce( min( case when Role_Description = 'AR_Cash_Acct' then Account_Number end),'') as Role3_Acct
...
From
FundsAccounts FA
join
Accounts A on FA.Acct_ID = A.Acct_ID
join
Funds F on FA.Fund_ID = F.Fund_ID
Where ...
group by Fund_Number
order by Fund_Number
* Noel -
A thing of beauty!
Thank you Noel.
jmatt
-
You are Welcome
* Noel -
"1. Funds
Fund_ID int PK
Fund_Number varchar(10)
2. Accounts
Acct_ID int PK
Account_Number
3. FundsAccounts
FundsAccounts_ID int PK
Fund_ID int
Acct_ID int
Role_Description varchar(20)"
I have to query/challenge your starting presumption of automatically using a 'generated' key for the primary keys. Each table should have a primary key determined by it's natural key ...where possible...and if none are obvious, invent a key. You seem to have started at the 2nd step.
I would advocate below....the desired end result can still be achieved. In fact with your existing design, you have uniqueness issues to handle with the design of the 3rd table.
1. Funds
Fund_Number varchar(10) PK
2. Accounts
Account_Number ???? (PK)
3. FundsAccounts
FKFund_Number varchar(10) - PK
FKAccount_Number ???? - PK
Description varchar(20)"
-
Thanks for your relpy Andrew,
I used a generated key as opposed to the more "natural" compound Account_ID + Fund_ID key because an account can play more than one role for a fund which would have resulted in a uniqueness violation for the compound key.
jmatt
-
Aha...well that bit (an account can play more than one role for a fund) was left out of the description.
But it may also point to a need to go down the route of.....your data model still feels 'short/understated'....and the generated key should nearly be the key-of-last-resort.
"1. Funds
Fund_Number varchar(10) PK
2. Accounts
Account_Number ???? (PK)
3. FundsAccounts
FKFund_Number varchar(10) - PK
FKAccount_Number ???? - PK
FKFund_ROLE ???? - PK
Description varchar(20)"
-
Andrew,
To quote from my original post, "The same account can play more than one role for a fund. That is why I have a FundAccounts_ID Primary Key instead of a compound key of Fund_ID and Acct_ID in the junction table".
I see your point and the possibilty of the 3 part "natural" key.
Thanks again for you input!
jmatt
-
Despite the occasional existence of a "natural key", I still prefer to alway use a generated key ID. All too often the natural key is changed by powers outside of your control. And manually changing all of the references to the "new natural id" is always a mess.
Have you never had a bank account changed to a new account number? What do you do when senior management decides that fund abc-12345 is now going to be named FX-abc=12345?
For primary keys or unique identifiers, Id stick with something owver which the DBA has total control.
-
Sara,
Thanks for your thoughtful response. I agree with your reasoning and almost always use generated keys, and will in this case.
jmatt
-
"Have you never had a bank account changed to a new account number? What do you do when senior management decides that fund abc-12345 is now going to be named FX-abc=12345?"
I'd hate to see your auditors!...or more to the point I don't think they would be happy with this policy. The whole point of a key is that it's value should be constant for it's entire life. In your example, what management more than likely are asking for is the 'description/name' of the account to change...not the underlying account code.
In accounting practice, if the old account has a requirement to have it's core identity renamed, the old account by right should be ruled/closed off and the balance transferred to a new account, with the last + first transactions on the relevant accounts identifiying where the balance has gone/come from.
Otherwise, your money could become my money with no history of the change!!!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply