Modeling Many-to-Many-to-Many relationship? (1 Viewer)

AZ_CC · Sep 19, 2019

I've done some poking around, but haven't been able to get my head around this. I'm working on modeling expenditures for a non-profit, and I'm not sure how to proceed because there seems to be more than one many-to-many relationship involved. A given expenditure will have 1 or more funding sources attached to it, as well as 0,1, or many clients served. Obviously, each of those funding sources or clients will also potentially be attached to multiple expenditures.

I can model either of these easily with a junction table, but I'm not sure how to handle both. Can I use a single junction table for this purpose? If not, I do need to relate the two (funds and clients); it seems like I could do that using the shared Expenditure key value even with two separate junction tables, but I've never done it before.

What should my table structure look like here?

MajP · Sep 19, 2019

I think this is two many to many not a single many to many
you would have

Code:

tbl_Expenditures_FundingSource
  expID_fk
  FS_ID_fk

and

Code:

tbl_Expenditures_Clients
  expID_fk
  ClientID_fk

not

Code:

tbl_Expenditures_Clients_Funding
  expID_fk
  FS_ID_fk
  clientID_FK

The reason is (i think) any expenditure has as set of funding sources and a set of clients. The latter table would be if for a given client the funding sources would be different for that expenditure.

arnelgp · Sep 19, 2019

you have to get your thoughts clear.
make some layout or mockup.
sample table structure.

AZ_CC · Sep 19, 2019

MajP said:
I think this is two many to many not a single many to many
you would have

Code:

tbl_Expenditures_FundingSource expID_fk FS_ID_fk

and

Code:

tbl_Expenditures_Clients expID_fk ClientID_fk

not

Code:

tbl_Expenditures_Clients_Funding expID_fk FS_ID_fk clientID_FK

The reason is (i think) any expenditure has as set of funding sources and a set of clients. The latter table would be if for a given client the funding sources would be different for that expenditure.

This is what I was leaning towards. I'm just not entirely sure how it will work to then relate Client expenditures made for them in different funding sources.

I can see how you can 'follow' the key trail (this Client is tied to this ExpenditureID via the junction table, which it tied to this set of Funding IDs in *that* junction table), so I think I can figure out how to produce queries and reports that show, for example, how much of Funding ID 1 has been expended on Client ID 14.

MajP · Sep 19, 2019

I am correct to say?
Expenditures have a set of funding streams (and funding streams have a set of expenditures)
Expenditures have a set of clients. (and clients have a set of expenditures)

But for a given client you do not get a different set of funding streams for an expenditure. If this is not correct you need the a single table.

To answer your question

tblClients joined to tbl_Expenditures_CLients by client id
joined to
tbl_Expenditures_FundingStream by expenditure id
joined to
tblFundingStream by funding stream id.

Pat Hartman · Sep 19, 2019

Do you actually keep the funding source --> client detail? That's a big task. If you buy a person a coat, does it really matter that 10 cents came from me and 9 cents came from MajP and 6 cents came from x and 13 cents came from y? Or should your records simply say $100 came from the winter clothing fund?

When I make contributions to Good Will, I don't specify how they should spend the money although I suppose if my contributions were large enough, they might let me say they should be used for building occupational training centers rather than just going into their general fund. Sometimes when I make contributions to my high school, I do specify what types of programs they should be used to support but more often than not, they just go to the general fund and the school spends the money where it feels it will do the most good.

AZ_CC · Sep 19, 2019

Pat Hartman said:
Do you actually keep the funding source --> client detail? That's a big task. If you buy a person a coat, does it really matter that 10 cents came from me and 9 cents came from MajP and 6 cents came from x and 13 cents came from y? Or should your records simply say $100 came from the winter clothing fund?

When I make contributions to Good Will, I don't specify how they should spend the money although I suppose if my contributions were large enough, they might let me say they should be used for building occupational training centers rather than just going into their general fund. Sometimes when I make contributions to my high school, I do specify what types of programs they should be used to support but more often than not, they just go to the general fund and the school spends the money where it feels it will do the most good.

Yes. Our grant funders absolutely *do* require that we be able to tie specific funding expenditures to specific clients. We were literally audited on being able to produce this last week, and it's part of the reason we currently maintain a number of *separate* excel sheets and Access databases. Other aspects of our client tracking and reporting systems have been unified, but our financial expenditures records are currently kept in funding source specific systems.

It's not common that a given expenditure for a client or clients are split between multiple funding sources, maybe 5-10% of the time, but it does happen.

I have also considered simply creating multiple expenditure records - if we spend $100 on coats, for example, and get $60 from the general fund and $40 from Funder Y, we would then simply create one expenditure for General for $60 and another for $40 for Funder Y. This is essentially what we do now, with the two expenditures logged in two separate access databases or spreadsheets. I...think?...this is acceptable in normalization terms, but it somehow seems inaccurate to split what was in reality a single expenditure (we spent $100) into two table entries (of $40 and $60).

Pat Hartman · Sep 19, 2019

As long as you have to maintain this money trail, you will need to have a separate row for each funding source for an expenditure.

Client1, coat, general fund, $60
Client1, coat, donorX, $40

Doesn't matter if it is .005% of the time or 100% of the time. If you have to have the split based on money source, you have to have it.

How you do this can be complicated and depends on how you want to handle the data entry. For example, if you want to enter $100 as the total expenditure and then create detail records to allocate each part of it, you need to work either within the constraints of a transaction or in a temp table. When the detail is entered and validated that 100% has been allocated and the sum of the individual transactions = the dollar amount on the header, then you copy the data from the temp table and append it to the main table followed by deleting it from the temp table. Updating a record has the same complexity.

We can help you when you get to that point.

AZ_CC · Sep 20, 2019

Pat Hartman said:
As long as you have to maintain this money trail, you will need to have a separate row for each funding source for an expenditure.

Client1, coat, general fund, $60
Client1, coat, donorX, $40

Doesn't matter if it is .005% of the time or 100% of the time. If you have to have the split based on money source, you have to have it.

How you do this can be complicated and depends on how you want to handle the data entry. For example, if you want to enter $100 as the total expenditure and then create detail records to allocate each part of it, you need to work either within the constraints of a transaction or in a temp table. When the detail is entered and validated that 100% has been allocated and the sum of the individual transactions = the dollar amount on the header, then you copy the data from the temp table and append it to the main table followed by deleting it from the temp table. Updating a record has the same complexity.

We can help you when you get to that point.

Okay, I think I'm getting closer. I'd need to have one table, tbl_Transactions, that included all the common information that's not dependent upon the clients or program specific expenditures. Then, I'd need two junction tables, one each for the client and funding table that included any info dependent upon those keys. So I might have something like:

Code:

tbl_Transactions: transID_PK, transDate, transPaymentType
jtbl_YouthTransactions: youthtransID_PK, youthID_FK, transID_FK
jtbl_FundingTransactions: fundtrans_IDPK, fundID_FK, transID_FK, ft_Ammount, ft_AccountingCode

And then I could build data entry around a Transactions form and client and funding subforms. I could also build a master summary query that included all of these fields to summarize the client level totals; I haven't done that before in this way, but the key associations are clear, and that should work to give me a report on program amounts spent on a given client, unless I'm missing something.

Pat Hartman · Sep 21, 2019

You don't have two junction tables. You have one table that combines all three foreign keys plus a date and an amount.

The tricky thing about the relationship is going to be that you have to ensure that you don't allocate more than 100% of any donation.

AZ_CC · Sep 23, 2019

Pat Hartman said:
You don't have two junction tables. You have one table that combines all three foreign keys plus a date and an amount.

The tricky thing about the relationship is going to be that you have to ensure that you don't allocate more than 100% of any donation.

That doesn't make sense to me, though - there will be more than one amount when there is more than one funding source. Is that intentional, to keep the client tied more closely to that funding source then just via the transaction ID?

Mark_ · Sep 23, 2019

AZ_CC,

Can one funding source go to more than one expenditure?

Pat Hartman · Sep 23, 2019

That doesn't make sense to me, though - there will be more than one amount when there is more than one funding source

The structure I proposed supports that. If you want to add a unique index to enforce a business rule, you will need an index with either the three FK's if only a single contribution for a person and an expense can come from each donor OR if the same donor can make multiple contributions to a single person/expense, then you would use the three FK's plus the date.

The amount from each funding source will generate a separate record.

You could break this into two tables but technically it is a single m-m relationship with a 1-m hanging off that. So the Client/Expense is the m-m and that table will now need an autonumber PK because the child table will be ClientExpenseID, DonorID, ContributionDate, Amount

I didn't split the table originally but it is not wrong to do so if it is easier for you to understand or if you see the potential for other child tables or attributes that are related to Client/Expense but NOT to donor in this context.

Modeling Many-to-Many-to-Many relationship? (1 Viewer)

AZ_CC

Registered User.

MajP

You've got your good things, and you've got mine.

arnelgp

..forever waiting... waiting for jellybean!

Attachments

AZ_CC

Registered User.

MajP

You've got your good things, and you've got mine.

Pat Hartman

Super Moderator

AZ_CC

Registered User.

Pat Hartman

Super Moderator

AZ_CC

Registered User.

Pat Hartman

Super Moderator

AZ_CC

Registered User.

Mark_

Longboard on the internet

Pat Hartman

Super Moderator

Similar threads

Users who are viewing this thread