database help - IT help desk software

Guys,

We use a system at work called Heat, it holds contract information and allows you to create support calls. Behind the scnes it is just sql. I am trying to make the changes below, but if you think of it in terms of an access or sql database heat works along simular lines...


ABC company is the company I work for
We have a number of customers with computer equipment under support.
A Customer will have a number of products.
Each product has a part code (generic) and a unque serial number.

Support contracts Currently:
At the moment the customer has a contract with ABC company for support. They have a contract number and
are charged a % of the list price for support. Each product has an ABC contract associated with it, a
level of support (4 hour on site for example) and each contract has a start and end date.
The contract lits the kit they have, the serial numbers, type of support and price for that support.

Changes:
Due to changes at ABC company and the manufacture of the products each customer product can now have
- different service levels from ABC company under the same contract. One product can have 4 hour on-site support and telephone support. These are two different items on the contract, both associated with the same product
- A contract for support with the manufacturer, called 'clevernet'. The manufactures of the product provids a contract to the customer entitling them to software upgrades. The clevernet contract will have one contract number that covers multiple customer products. Also products can be covered by more than one clevernet contract, for example they may have a clevernet for minor upgrades and a clevernet for major upgrades. So the customer is likely to have a number of clevernet contract numbers (max likely to be 5 or 6)
The clevernet contracts have start and end dates, but customers can add additional products to their clevernet contract at any time, so every product covered by one clevernet contract may not have the same start and end date for clevernet support. Generally they will but this exception may well happen.
The clevernet's will always be bought via ABC company and will always have an ABC company contract number associated with them. However the start and end dates for the clevernet contract maybe different to the start and end dates for the ABC company contract. For example the customer could pay for 3 years hardware support with ABC company and only 1 year of clevernet on the products (or the other way around).
Typically the ABC support and clevernet support will be bought at the sametime (new install) but it is equally possible that a customer has one contract runnning with ABC company for hardware support and later buys clevernet support, giving them a second ABC contract number and clevernet number(s)

Ideally we want to be able to lookup a product and see all the types of support it has on it, including the relevant contract numbers for both ABC support and clevernet support, and also be able to create a contract from the ABC company contract number that shows:
each product with its ABC support types (and prices) and its cleveret contracts (and the price for the clevernet for that piece of kit). The contract will then show everything associated with that contract number.
I understand how one contract number can be associated to multiple products, but don't understand how to intergrate the clevernet as a sub contract that has an ABC contract number associated to it but also a clevernet contract number that is associated to multiple products.
I can see how the clevernets could go in as completely seperate contracts, but we really want them to appear as part of the overall customer contract

I have added additional fields to the config\customer equipt table (the table that holds the info on each piece of kit under support) so that each customer product can have a 'contractno1', 'contractno2', 'contractno3'fields along with fields for dates, types of support etc but I am not sure if this is the best way to achieve this?

It does give the ability to add the additional contract details and associate them with the products including giving unique start and end dates for the clevernet (since the dates are stored on customer equipt table, however I think it would then be difficult to pull the information back out? Also there is no validation of the clevernet contract number and its associated ABC company contract number.


I anyone could give me some pointers of how to achieve contracts with subcontracts in heat it would be greatly appreciated...

Any questions or pointers to better solutions also very welcome


thanks
Tom