I am in the planning stages of my application and have come across a problem I am not sure how to approach. In this business model it is typical to generate an estimate for a client and that estimate will either be picked up and converted to a Contract and then an Invoice, or, it will languish for months in the estimates file and never become a contract.
I am not sure how to convert an Estimate Record into a Contract without duplicating all the data. I could set up two sets of tables, one set for Estimates and one set for Contracts (and sets for Work Orders and Invoices also) but there's something inside me that says I should be able to save on duplicating all the data since the detail items do not generally change as the Estimates progress to Contracts, Work Orders, and finally Invoice. The data is essentially the same in all these forms, only the header information changes.
Should I set up one detail table and have a field for each type of header file I will need, that is a Est_No field, Cont_No field, Work_Ord_No field and a Inv_No field? If I need to edit the Estimate details and re-submit I'll just generate a new Est_No and delete the outdated record?
Does this make any sense? I'm thinking my sets will look something like this:
Estimate Set -
Parent: Est Header
Child: Est Detail
Link: Est_No
Contract Set -
Parent: Cont Header
Child: Est Detail
Link: Cont_No
Work Order Set -
Parent: WO header
Child: Est Detail
Link: WO_No
Invoice Set -
Parent: Inv Header
Child: Est Detail
Link: Inv_No
I'm having a hard time getting my head around this. It's probably not as complicated as I want to make it. I just don't want to set up the database wrong to start with. I'd like to get the table structures right at least and then I can adjust and tweak the application as I go.
Thanks for the help. I've read some great stuff on this board. I'm an amatuer developer but my day job is running our retail fireplace store.
Sean Kennedy
I am not sure how to convert an Estimate Record into a Contract without duplicating all the data. I could set up two sets of tables, one set for Estimates and one set for Contracts (and sets for Work Orders and Invoices also) but there's something inside me that says I should be able to save on duplicating all the data since the detail items do not generally change as the Estimates progress to Contracts, Work Orders, and finally Invoice. The data is essentially the same in all these forms, only the header information changes.
Should I set up one detail table and have a field for each type of header file I will need, that is a Est_No field, Cont_No field, Work_Ord_No field and a Inv_No field? If I need to edit the Estimate details and re-submit I'll just generate a new Est_No and delete the outdated record?
Does this make any sense? I'm thinking my sets will look something like this:
Estimate Set -
Parent: Est Header
Child: Est Detail
Link: Est_No
Contract Set -
Parent: Cont Header
Child: Est Detail
Link: Cont_No
Work Order Set -
Parent: WO header
Child: Est Detail
Link: WO_No
Invoice Set -
Parent: Inv Header
Child: Est Detail
Link: Inv_No
I'm having a hard time getting my head around this. It's probably not as complicated as I want to make it. I just don't want to set up the database wrong to start with. I'd like to get the table structures right at least and then I can adjust and tweak the application as I go.
Thanks for the help. I've read some great stuff on this board. I'm an amatuer developer but my day job is running our retail fireplace store.
Sean Kennedy
Comment