Is this database normalised?

  • Thread starter Thread starter Tony Williams
  • Start date Start date
T

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
 
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.
 
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.
 
nope

Contract
ContractID
.....

Party
PartyID
.....

ContractParty
ContractID
PartyID

Pieter
 
nope

Contract
ContractID
.....

Party
PartyID
.....

ContractParty
ContractID
PartyID

Pieter
 
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.
 
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.
 
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
 
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
 
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
 
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
 
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?
 
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?
 
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
 
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
 
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
 
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
 
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.
 
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.
 
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.
 
Back
Top