Hello,
I'm designing a contract and payment processing database for a friends construction company. I've made an attempt at designing the data model, but wondered if anyone out there had ever come accross a similar scenario and could be of any help. I have attached a copy of my initial ERD.
Entitys are as follows:
Company
Holds details of the company that are operating the system.
Customer
Billing details for the customer.
Site
A customer has many different sites at which work is undertaken.
Contract
A contract is created for all work undertaken at a customer site.
Plot
The work a contract covers is carried out on various different plots at a customer site.
Stage
Work is carried out in stages on a plot by plot basis. When a work stage is complete the stage can then be invoiced for payment.
Invoice
Once work stages are complete an invoice can be raised. An invoice can be for one or more stages (always under the same contract).
Invoice_Line
Details of the different stages billed under a single invoice.
Payment
Stores detail of a customer payment.
Payment_Allocation
A single payment may be split accross multiple invoices. A single invoice may be paid through multiple payments.
The main area I was hoping for help with is payment allocation, the requirements of which are listed below:
1. When a payment is received it must be possible to split it accross multiple invoices (a payment will only ever be for invoices within a single contract).
2. It must be possible for an invoice to be paid in part (i.e. be paid by mupltiple payments).
3. If a payment isn't allocated in it's entirity, the the remainder should be put to a payment on account for allocation at a later date.
4. An entire payment should be able to be put to a payment on account and then allocated later.
5. It must be possible to enter credits against invoices.
The reason I have Contract joined to Payment_Allocation is so that a payment on account can be created and linked back to the contract, for allocation at a later date. If a payment on account is stored as an entry in the payment_allocation table it will become difficult to then allocate the payment on account to invoices and keep an audit trail. The payment on account isn't really allocated until it is put against an invoice so I'm loathe to do it this way.
On the other hand if I store the payment on account in the payment table (until allocated) it may make it harder for me to produce my statement report.
I'm also having difficulty seeing how I can build a statement of account from these two tables...do I need to build up a transaction table in order to do this effectively? Or a temporary table of transations to base my report on?
Any advise or pointers would be very helpful.
Thanks in advance for your time.
webbmatt
I'm designing a contract and payment processing database for a friends construction company. I've made an attempt at designing the data model, but wondered if anyone out there had ever come accross a similar scenario and could be of any help. I have attached a copy of my initial ERD.
Entitys are as follows:
Company
Holds details of the company that are operating the system.
Customer
Billing details for the customer.
Site
A customer has many different sites at which work is undertaken.
Contract
A contract is created for all work undertaken at a customer site.
Plot
The work a contract covers is carried out on various different plots at a customer site.
Stage
Work is carried out in stages on a plot by plot basis. When a work stage is complete the stage can then be invoiced for payment.
Invoice
Once work stages are complete an invoice can be raised. An invoice can be for one or more stages (always under the same contract).
Invoice_Line
Details of the different stages billed under a single invoice.
Payment
Stores detail of a customer payment.
Payment_Allocation
A single payment may be split accross multiple invoices. A single invoice may be paid through multiple payments.
The main area I was hoping for help with is payment allocation, the requirements of which are listed below:
1. When a payment is received it must be possible to split it accross multiple invoices (a payment will only ever be for invoices within a single contract).
2. It must be possible for an invoice to be paid in part (i.e. be paid by mupltiple payments).
3. If a payment isn't allocated in it's entirity, the the remainder should be put to a payment on account for allocation at a later date.
4. An entire payment should be able to be put to a payment on account and then allocated later.
5. It must be possible to enter credits against invoices.
The reason I have Contract joined to Payment_Allocation is so that a payment on account can be created and linked back to the contract, for allocation at a later date. If a payment on account is stored as an entry in the payment_allocation table it will become difficult to then allocate the payment on account to invoices and keep an audit trail. The payment on account isn't really allocated until it is put against an invoice so I'm loathe to do it this way.
On the other hand if I store the payment on account in the payment table (until allocated) it may make it harder for me to produce my statement report.
I'm also having difficulty seeing how I can build a statement of account from these two tables...do I need to build up a transaction table in order to do this effectively? Or a temporary table of transations to base my report on?
Any advise or pointers would be very helpful.
Thanks in advance for your time.
webbmatt
Attachments
Last edited:
