Is this database normalised?

[Tony Williams]

I have a database with 4 tables. Table 1 holds the details of contracts.
Tables 2, 3 and 4 holds details of the parties to the contract. Contracts
can have 1 2 or 3 parties apart from ourselves. I have created separate
tables for the parties to hold information like contact names address etc.
Is this the right formation for this database?
Thanks
Tony

 

[Douglas J Steele]

Are you saying that table 2 is the details for contact 1, table 3 is the
details for contact 2 and table 4 is the details for contact 3?

Odds are it isn't normalized, then. Having multiple tables for the same
entity isn't usually correct.

 

[Douglas J Steele]

Are you saying that table 2 is the details for contact 1, table 3 is the
details for contact 2 and table 4 is the details for contact 3?

Odds are it isn't normalized, then. Having multiple tables for the same
entity isn't usually correct.

 

[Pieter Wijnen]

nope

Contract
ContractID
.....

Party
PartyID
.....

ContractParty
ContractID
PartyID

Pieter

 

[Pieter Wijnen]

nope

Contract
ContractID
.....

Party
PartyID
.....

ContractParty
ContractID
PartyID

Pieter

 

[Terry Kreft]

Probably not. I would expect 3 tables

contracts
parties
contractsparties

Where
contracts - holds the details about a contract
parties - holds details about parties to contracts
contractsparties - links the two together

In this way you can have as many parties to a contract as you like and have
as many contracts for a party as you like.

 

[Terry Kreft]

Probably not. I would expect 3 tables

contracts
parties
contractsparties

Where
contracts - holds the details about a contract
parties - holds details about parties to contracts
contractsparties - links the two together

In this way you can have as many parties to a contract as you like and have
as many contracts for a party as you like.

 

[Tony Williams]

Would the parties table then have to have fields like company1name,
company2name, company3name and then contact1name,contact2name,contact3name
etc
I htought that this was not a good way to set up a database ie having
repeated fields. What would be the structure of contractparties, how would
it link the parties to the contacts?
Thanks for your help
Tony

 

[Tony Williams]

Would the parties table then have to have fields like company1name,
company2name, company3name and then contact1name,contact2name,contact3name
etc
I htought that this was not a good way to set up a database ie having
repeated fields. What would be the structure of contractparties, how would
it link the parties to the contacts?
Thanks for your help
Tony

 

[Terry Kreft]

Good grief no.

contracts would be something like
contractID
contractTitle
contractDetails
... (other stuff to do with a contract)

parties would be something like
partyID
companyName
contactName
... (other stuff to do with a party to a contract)

contractsparties would be something like
contractID
partyID
... (other stuff to do with a contract and party combination)

Data might look like this

contracts
-----------
contractID contractTitle contractDetails
1 Tiitle1 Do something
2 Tiitle2 Do something else
3 Tiitle3 Something else again

parties
--------
partyID companyName contactName
A Acme Ltd Fred Bloggs
B Steptoe & Son HH Corbett
C Specs & Co S Smith

contractsparties
-----------------
contractID partyID
1 A
1 B
2 A
2 C
3 A
3 B
3 C


You could then do something like

SELECT
contracts.contractTitle,
parties.companyName
FROM
contracts
INNER JOIN
contractsparties
ON
contracts.contractID = contractsparties.contractID
INNER JOIN
parties
ON
parties.partyID = contractsparties.partyID
WHERE
contracts.contractID = 2

And get back
contractTitle companyName
------------- ----------------
Tiitle2 Acme Ltd
Tiitle2 Specs & Co

 

[Terry Kreft]

Good grief no.

contracts would be something like
contractID
contractTitle
contractDetails
... (other stuff to do with a contract)

parties would be something like
partyID
companyName
contactName
... (other stuff to do with a party to a contract)

contractsparties would be something like
contractID
partyID
... (other stuff to do with a contract and party combination)

Data might look like this

contracts
-----------
contractID contractTitle contractDetails
1 Tiitle1 Do something
2 Tiitle2 Do something else
3 Tiitle3 Something else again

parties
--------
partyID companyName contactName
A Acme Ltd Fred Bloggs
B Steptoe & Son HH Corbett
C Specs & Co S Smith

contractsparties
-----------------
contractID partyID
1 A
1 B
2 A
2 C
3 A
3 B
3 C


You could then do something like

SELECT
contracts.contractTitle,
parties.companyName
FROM
contracts
INNER JOIN
contractsparties
ON
contracts.contractID = contractsparties.contractID
INNER JOIN
parties
ON
parties.partyID = contractsparties.partyID
WHERE
contracts.contractID = 2

And get back
contractTitle companyName
------------- ----------------
Tiitle2 Acme Ltd
Tiitle2 Specs & Co

 

[Tony Williams]

Thanks Terry I sit and work through your reply over the next couple of days
and perhaps post any specific questions in the relevant newsgroup.
Thanks again
Tony
PS I sent you an email recently asking if you had produced a copy of
wzForms.zip for Access 2002. I tried installing the copy from the website
and it says it was designed for a previous version? But the email kept
bouncing back as undeliverable?

 

[Tony Williams]

Thanks Terry I sit and work through your reply over the next couple of days
and perhaps post any specific questions in the relevant newsgroup.
Thanks again
Tony
PS I sent you an email recently asking if you had produced a copy of
wzForms.zip for Access 2002. I tried installing the copy from the website
and it says it was designed for a previous version? But the email kept
bouncing back as undeliverable?

 

[Tony Williams]

Just one point,though I'm not sure I follow how I would create the
contractparties table? Would the three tables be linked and therefore the
contractparties table would be created automatically from the other two. Eg
a new record in contracts would create the ID in contracts and in
contractparties and a new record in parties would create an ID record in
contractparties as well?
Sorry to be so dim!!!
Thanks
Tony

 

[Tony Williams]

Just one point,though I'm not sure I follow how I would create the
contractparties table? Would the three tables be linked and therefore the
contractparties table would be created automatically from the other two. Eg
a new record in contracts would create the ID in contracts and in
contractparties and a new record in parties would create an ID record in
contractparties as well?
Sorry to be so dim!!!
Thanks
Tony

 

[Tony Williams]

Thinking more about this. Would this work if I just had two tables,
contracts and parties and they were linked by say a field called txtrefnbr
which was a key field in contracts and was a field in parties which could
any number of parties to one contract which is what we want?
Thanks
Terry

 

[Tony Williams]

Thinking more about this. Would this work if I just had two tables,
contracts and parties and they were linked by say a field called txtrefnbr
which was a key field in contracts and was a field in parties which could
any number of parties to one contract which is what we want?
Thanks
Terry

 

[Rick Wannall]

No.

If you want to have multiple contacts on any givent contract, and you need
any contact to be able to participate in multiple contracts, you need the 3
tables already described by others in great detail.

They are consistent in their answers for a reason. Heed them.

 

[Rick Wannall]

No.

If you want to have multiple contacts on any givent contract, and you need
any contact to be able to participate in multiple contracts, you need the 3
tables already described by others in great detail.

They are consistent in their answers for a reason. Heed them.

 

[Terry Kreft]

Yes.

The schema I showed would allow any number of parties to participate in any
number of contracts. This is called a many to many or M:M relationship.

What you describe below would allow any number of parties to participate ina
a single contract. This is called a one to many or 1:M relationship.