Trying to wrap my mind around a design (and its a small mind)

[randerson]

First I appologize for the length of this post. I am trying to work out the best design for the following business requirements. Basically the system tracks contracts and task orders (children of contracts) for contracts that we have been award and for subcontracts we award. First let me give you some definitions

1) A contract may have one or more task orders (called an IDIQ contract) or a contract will never have task orders (Non-IDIQ). Basically an IDIQ contract defines general types of work and an individual task order under that contract is for a specific task that falls under the scope of the contract. A Non-IDIQ contract is for a specific piece of work (pour a slab of contract 10’x10’, dig a 6’ deep hole, etc)

2) A specific contract is awarded to a single contractor by a single customer.

3) A task order must have one and only one parent contract. (basic one to many relationship between contract and task order).

Now here is the relationship I am trying to model. A contract can be awarded to us (Prime contract) and we, in turn, may award a contract to sub-contractors (Sub Contract) to help us on this contract. The contracts may be IDIQ or Non-IDIQ and so may the sub-contracts. So a Non-IDIQ prime contract may have associated IDIQ Contracts/Task Orders and vice versa. The usual relationship is a IDIQ Prime contract with many Prime Task Orders and for each Prime Task Order there may be one or more Non-IDIQ Sub Contracts.

I am keeping the Prime Contract and Sub Contract data in the same table (all the data elements for both a Prime Contract and Sub Contract are the same as are the Task Order data elements). I am trying to resolve the relationship between Contracts/Task Orders and Sub Contracts/Task Orders by using a thirds XREF type table. The following are abbreviated table designs

Table Design (Abbreviated)
Contract Table
Contract_Seq PK
Contract Number
IDIQ_Flag

Task Order Table
TO_Seq PK
Contract_Seq FK to Contract table
TO_Number

Contract_SubContract_XREF
Prime_Contract_Seq Not Null
Prime_TO_Seq
Sub_Contract_Seq Not Null
Sub_TO_Seq

I am having problems figuring out if this is the best way to model this relationship and also figuring out the sql to get the correct data out of this relationship. Can anyone give me some suggestions as to if this is the best way to model this relationship or if there is a better way. I can send an ER Diagram to anyone interested if that will help.

Thanks very much
Richard Anderson

[jomoueea]

Please send the ER - Diagram to this e-mail
jomoueea@hotmail.com

Thanks
Aiman Al Jumoay

[jomoueea]

Dear Friend,
I am sure that you have conmments on the ERD because it is only preliminary , please update me with your fine comments
and another thing I would like to know about XREF ?? I do not know anything about it , if there is a documentation link I would appreciate it

Thanks
Aiman Al-Jumoay

[simply_dba]

Just a question over here.
Is there any possibility that a particular task order/nonidiq contract may be given to more than one sub contracters?
This question is unanswered in the ERD and even in the original post

[jomoueea]

Dear Simply_dba ,
is it possible to give a contract more than Contractor, I mean is it logically possible and if so , does not the entity in the middle between the Contracts and the contractors.

Thanks
Aiman Al-Jumoay

[simply_dba]

is it possible to give a contract more than Contractor, I mean is it logically possible

Yes it may be. One contract being shared by more than one contractors.
Otherwise why would you create a contract_seq when contract number is there ?
And Sorry, yes your entity 6 takes care of it.
Missed it previously