I want to create a contract management system. I have information about the contracts, contractors, dates, payments and so on and would like to know the best way to organize it in tables - which tables I should create and which fields they should have.

The fields are:

1. Contract number
2. Contractor name - A contractor can have several contracts
3. Supervisor in-charge of contractor
4. Unit the contractor works in
5. Service type
6. Transaction type
7. Committee Date – the date the committee decides to realize a contract
8. Protocol number
9. Original date – Start of first work
10. valid until – date when the contract ends
11. renew date – date when the contract starts
12. units – unit of payment
13. Quantity – number of works the contractor will do in the contract
14. Quantity2 – number of works (in a special case)
15. Contract price – the price of one work
16. Payment per month – multiplication of Quantity and Contract price
17. Number of months – length in month of the contract
18. total payment in contract – number of months X Payment per month
19. total payment in year

And some supervision fields:

20. Date sent to contractor
21. Date returned from contractor
22. Date sent to financial committee
23. Date returned from financial committee
24. Date of completion


Thanks.

Start by thinking about and identifying how many 'things' your desired results involve. Each type of 'thing' requires one table. After you have identified the 'thing,' you can then determine what data belongs in which table. Each field you include for data in the table should describe something about the subject of the table. Then you can start to determine how each table relates to other tables.

For example, there are several different things implied by the list you have posted. You have contracts, contractors, personnel (if the supervisor is one of your people), contract activities and contract details for a start. You would not store items 16, 17, 18, or 19. These are calculated values and should be calculated when needed either queries or on forms or reports.

The supervision 'fields' you have listed should not be separate fields in the database. Most likely they belong in the contract activities table as idividual records. That table would need to include an activity type indicator and that implies an additonal table for activity types that could then be used as the row source for combo boxes on the relevant forms. The committed date is also one of these contract actiivites.

You may find sketching your tables and field assignments helpful in doing this analysis. Pencil, paper, and eraser are your best tools at this stage. Don't be afraid to modify your design until you get it right. It is fair easier to deal with design flaws before you start to actually create your database objects.

If you are fairly inexperienced with database design you should review some database and access fundamentals. These links provide background material to guide you.

• Database planning tutorial Part I
• Database planning tutorial Part II
• argeedblu's FAQ on Design Strategies
• Noah's FAQ on Normalization
• Jerry's FAQ on Normalization
• GroverParkGeorge's PDF on Normalization
• Jerry’s FAQ on Fully Qualified Naming
• Jerry's FAQ on Glossary
• Danny's FAQ on Reserved Words
• Autonumbers - What They Are / Are Not
• Primary Keys - Natural vs Surrogate
• Naming Conventions - Hungarian Notation
• Crystal's ACCESS Basics for Programming
• Database Answers (free models)
• MSKB 283878: Description of the database normalization basics

Glenn

Hi Glenn,
Thank you for your reply, it is great help.
There are a couple of things, tough, that I did not fully understand :
First, you wrote that items 16, 17, 18 and 19 should be calculated values. I would like to put them in a form, but if they are not stored values, then how will they be stored into the database, so taht the system could keep track of old values ?

Second, I did not fully understand the structure of the activities table. What do you mean by "activity type indicator" and what is the relationship between the activities type table and the activities table ?

What you would store would be values like rates and quantities. If rates are subject to change, you will need a rate table with a field to indicate when the rate came into effect. Then when you want to show the values on a form, part of your calcuation would be to determine what rate was in effect on the date of the transaction. I am assuming here that a rate is effective until it is superseded by a subsequent rate and that each type of work has a standard rate.

If the situation is a bit simplery then you can store the applicable rate directly in the transaction record. The calculation would be the same, in any case, and it is the result of the calculation that you would disply on your form.

The activity activity types I had in mind are things like committee approval, award contract, start work, pause work, restart work, stop work, and so on. The activities table store one record for each activity that happens during the course of contract fulfillment. That lets you track all activity relevant to any given contract yet avoid having separate fields as you were thinking of in your original post.

Glenn

Thanks for the reply, I have another question, if you don't mind.

There are two ways to pay a contractor for a certain contract:
1. Payment in each month for the number of months the contract lasts.
2. One payment for the whole period of the contract.

In the first way we have the fields "Payment per month" and "Number of months" and from these fields we calculate the field "total payment for contract" :
"total payment for contract" = "Payment per month" X "Number of months"

In the second way we have "total payment for contract" and "Number of months" and we calculate from them "Payment per month".

How should I save the fields "Payment per month" and "total payment for contract", having that somtimes they are calculated automatically and sometimes they are inserted by the user (depending on which of the two ways mentioned above is used).
Maybe they should be inserted by the user in any case and when a calculation is required the user will do the calculation manually and insert the value.

Thanks.

I would save the number of payments and the full contract price and calculate the periodic amount and final payment on the form (in case the contract amount does not divide evenly.) You most likely should also be tracking payments and adjustments transactions so that you can calculate balance owing at any time.

Glenn