Table structure thoughts

[Anon]

I would like some suggestions on table design. The app I am writing has an accounts table and what I need to do is track the contracts and their version that the accounts have agreed to, the date they agreed to it, the expiration date of the contract, etc. Then in my PHP code I will need to check if any have expired or are expiring, generate reports on the accounts that haven't agreed to certain contracts, etc.
How many tables should I use? One for contracts and their versions, and one to relate the account and the acceptance date to that contract id?

Brian Snipes

[Anon]

You could use the structure you suggested.
some improvemnts (I am not sure if they are really required by your app) that will take some changes to the structure:
1. Save history of account-contract relatioships - e.g. let the user know that account 1 used contract 1 in 1998, contract 3 in 1999 and contract 19 in 2000.
2. for even greater details, you could have a CRM-like usage where the negotiations regarding the contract are documented, including all the offers, the customer's response, who handled it, etc.

Besides this, there's also room for other tables to store supporting data that you normalized out of the above tables, e.g. details of users of your application, department names, type of service sold, etc.

Yaron Yogev