What is the best way to handle this kind of scenario?
Given clients, many with multiple projects, and thus:
Clients table
Projects table
Invoice table
Payments table
===========
When a payment is made, often it will be for multiple invoices and often for several different projects. I need to be able to allocate the payments to the invoices, but also to the projects. Presumably, a 1:M set, Payments:Allocated would do the trick, BUT: the majority of payments are for a single invoice. So one check may be for a given invoice/project, and another may be for several projects. The client needs to be able to look at a project, and see what has been paid against that project. This is where it gets sticky. In order to correlate, you then have to examine both the parent & child tables, since there may be single payments, or multiples (child records) against the project.
What is the suggested method to normalize this equation?
Please keep in mind, there is also the need to relate clients to payments (which is easy since you only need to look at the actual parent records - i.e. payments).
See attached report snapshot to get an idea what I am talking about.
P.S. I have been using the 1:M method in this case for years, but I'm not satisfied due to the lack of normalization as described above.
Given clients, many with multiple projects, and thus:
Clients table
Projects table
Invoice table
Payments table
===========
When a payment is made, often it will be for multiple invoices and often for several different projects. I need to be able to allocate the payments to the invoices, but also to the projects. Presumably, a 1:M set, Payments:Allocated would do the trick, BUT: the majority of payments are for a single invoice. So one check may be for a given invoice/project, and another may be for several projects. The client needs to be able to look at a project, and see what has been paid against that project. This is where it gets sticky. In order to correlate, you then have to examine both the parent & child tables, since there may be single payments, or multiples (child records) against the project.
What is the suggested method to normalize this equation?
Please keep in mind, there is also the need to relate clients to payments (which is easy since you only need to look at the actual parent records - i.e. payments).
See attached report snapshot to get an idea what I am talking about.
P.S. I have been using the 1:M method in this case for years, but I'm not satisfied due to the lack of normalization as described above.
Comment