designing a relationship

[Guest]

I have a question, and maybe someone can give me the more effective of the two. I want to set up a bunch of relantion ships with this management software. But I will start with 2 tables.

One table [A] contains a project list, which consists of a Unique ProjectID and some other stuff like project name, description, etc.

Then table contains contracts with a Unique ContractID, contract info, etc.

Now, contracts will be under the projects, so 1 project can have many contracts. My question is: in the contract table, should I also have a field containing the ProjectID it falls under and link that field, OR, should I have a 3rd table [C] that has 2 fields: The ProjectID and the corresponding ContractID and connect them that way? I hope I am making sense. Thanks

 

[Tim Ferguson]

1 project can have many contracts. My question is: in the contract
table, should I also have a field containing the ProjectID it falls
under and link that field, OR, should I have a 3rd table [C] that has
2 fields: The ProjectID and the corresponding ContractID and connect
them that way?

If each contract belongs to exactly one project, then the Contracts table
needs a FK to hold the pointer to the project it belongs to. (Think about
what you would put into a Projects.ControlledByContractID field if there
were several projects.) For example:

For example:

Contracts
ContractID RefersToProject
========== ---------------
1023 45
1024 39
1025 45
1026 44
etc

If each contract can belong to lots of projects, then you'd need a new
table called IsMentionedIn to record every mention of each project in each
contract.

IsMentionedIn
ContractID ProjectID
========== =========
1023 45
1023 86
1024 39
1025 45
1025 86
1026 44
etc

Hope that helps


Tim F

 

[Roger Carlson]

It depends what the relationship is between Projects and Contract. Are the
following statements true:

Each Project contains One or More Contracts
Each Contract pertains to One And Only One Project.

If these are both true, then you have a one-to-many relationship and you
should put the ProjectID in the Contracts table (long integer -- NOT
autonumber) as a foreign key in the Contracts table:

Projects Contracts
====== =======
ProjectID--| ContractID
|----< ProjectID

On the other hand, if a Contract can be part of more than one Project, you
have a many-to-many relationship, which is created through the use of a
third table called a "linking" or "intersection" table, like this:
Projects PriojCont Contracts
====== ======= =======
ProjectID--| ContractID>---ContractID
|----< ProjectID


--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

I have a question, and maybe someone can give me the more effective of the

two. I want to set up a bunch of relantion ships with this management
software. But I will start with 2 tables.

One table [A] contains a project list, which consists of a Unique

ProjectID and some other stuff like project name, description, etc.

Then table contains contracts with a Unique ContractID, contract info, etc.

Now, contracts will be under the projects, so 1 project can have many

contracts. My question is: in the contract table, should I also have a field
containing the ProjectID it falls under and link that field, OR, should I
have a 3rd table [C] that has 2 fields: The ProjectID and the corresponding
ContractID and connect them that way? I hope I am making sense. Thanks!