Relationship/Normalizing

I have a database that I just can't visualize the relationships in.
The data going in is made up of
Company
Contract
Location
etc.

Each company may have several contracts, but one main contract. They may
also have several locations for each contract. I can relate the locations to
the contract, but I don't know how to relate the contracts to each other.
They do have the one common factor of the company name, but the real problem
is that the db is already set up and it is not normalized. It is just one
very big table. It was set up before me, and I am trying to normalize it and
make it a bit more usable.

Right now most of the contracts are each set up individually with duplicating
data for their contacts, address/phone/email etc. for those that are
associated with each other. They have one tab on a form that will list all
of the other contract numbers that are associated with it. The problem with
this is that each contract on that tab is it's own field, and I cannot search
easily for the other contracts if I don't know the main contract, because not
all of them are built individually.

I would greatly appreciate any suggestions on how I can update this DB so
that I can add the locations/associated contracts.

Thanks in advance.

--
Message posted via http://www.accessmonster.com

Over time, each company will have mulitple contracts. (Contracts cannot last
for ever.) Therefore you have a one-to-many relation between Company and
Contract.

Each contract can cover mulitple locations. Therefore you have a one-to-many
relation between Contract and Location. Therefore one contract will have
many line items, so these will be stored in a ContractDetail table.

Is it possible that a location could change hands over the years (e.g. if
one company sells it to another)? Or, are these locations towns where
different companies may be operating? If so, it's actually a many-to-many
relation beween Contract and Location. The following shows how to do that.

Location table:
LocationID AutoNumber primary key
LocationName Text

Company table:
CompanyID AutoNumber primary key
CompanyName Text
...

Contract table:
ContractID AutoNumber primary key
CompanyID Number relates to Company.CompanyID
ContractStart Date/Time when this contract starts
ContractEnd Date/Time when this contract needs renewing
...

ContractDetail table:
ContractDetailID AutoNumber primary key
ContractID Number relates to Contract.ContractID
LocationID Number relates to Location.LocationID

If a contract may not cover the same dates for all the locations, move the
ContractStart and ContractEnd fields into the ContractDetail table.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"cableguy47905 via AccessMonster.com" u4349@uwe wrote in message
news:7335ce625b5aa@uwe...
I have a database that I just can't visualize the relationships in.
The data going in is made up of
Company
Contract
Location
etc.

Each company may have several contracts, but one main contract. They may
also have several locations for each contract. I can relate the locations
to
the contract, but I don't know how to relate the contracts to each other.
They do have the one common factor of the company name, but the real
problem
is that the db is already set up and it is not normalized. It is just one
very big table. It was set up before me, and I am trying to normalize it
and
make it a bit more usable.

Right now most of the contracts are each set up individually with
duplicating
data for their contacts, address/phone/email etc. for those that are
associated with each other. They have one tab on a form that will list
all
of the other contract numbers that are associated with it. The problem
with
this is that each contract on that tab is it's own field, and I cannot
search
easily for the other contracts if I don't know the main contract, because
not
all of them are built individually.

I would greatly appreciate any suggestions on how I can update this DB so
that I can add the locations/associated contracts.

Thanks in advance.

"cableguy47905 via AccessMonster.com" u4349@uwe wrote in message
news:7335ce625b5aa@uwe...
I have a database that I just can't visualize the relationships in.
The data going in is made up of
Company
Contract
Location
etc.

Each company may have several contracts, but one main contract.
They may
also have several locations for each contract. I can relate the
locations to
the contract, but I don't know how to relate the contracts to each
other.
They do have the one common factor of the company name, but the real
problem
is that the db is already set up and it is not normalized. It is
just one
very big table. It was set up before me, and I am trying to
normalize it and
make it a bit more usable.

Right now most of the contracts are each set up individually with
duplicating
data for their contacts, address/phone/email etc. for those that are
associated with each other. They have one tab on a form that will
list all
of the other contract numbers that are associated with it. The
problem with
this is that each contract on that tab is it's own field, and I
cannot search
easily for the other contracts if I don't know the main contract,
because not
all of them are built individually.

I would greatly appreciate any suggestions on how I can update this
DB so
that I can add the locations/associated contracts.

Thanks in advance.



cableguy47905,

The following tables represent my best guess.

It will require interpretation on your part as to how to fit them into
your overall database schema.

You can copy and paste these DDL SQL queries each into an MS Access
Query, executing each one in order to create the tables and the
relationships.


CREATE TABLE Companies
(CompanyID AUTOINCREMENT
,CompanyName TEXT(96)
,CONSTRAINT pk_Companies
PRIMARY KEY (CompanyID)
)

CREATE TABLE Contracts
(ContractID AUTOINCREMENT
,ContractName TEXT(255)
,CONSTRAINT pk_Contracts
PRIMARY KEY (ContractID)
)

CREATE TABLE Locations
(LocationID AUTOINCREMENT
,LocationName TEXT(96)
,CONSTRAINT pk_Locations
PRIMARY KEY (LocationID)
)

CREATE TABLE CompanyContracts
(CompanyContractID AUTOINCREMENT
,CompanyID INTEGER
,ContractID INTEGER
,ContractStart DATETIME
,ContractEnd DATETIME
,CONSTRAINT pk_CompanyContracts
PRIMARY KEY (CompanyContractID)
,CONSTRAINT fk_CompanyContracts_Companies
FOREIGN KEY (CompanyID)
REFERENCES Companies (CompanyID)
,CONSTRAINT fk_CompanyContracts_Contracts
FOREIGN KEY (ContractID)
REFERENCES Contracts (ContractID)
,CONSTRAINT un_CompanyID_ContractID_ContractStart_ContractEnd
UNIQUE (CompanyID, ContractID, ContractStart, ContractEnd)
)

CREATE TABLE MainContracts
(CompanyContractID INTEGER
,CONSTRAINT pk_MainContracts
PRIMARY KEY (CompanyContractID)
,CONSTRAINT fk_MainContracts_CompanyContracts
FOREIGN KEY (CompanyContractID)
REFERENCES CompanyContracts (CompanyContractID)
)


CREATE TABLE ContractLocations
(ContractLocationID AUTOINCREMENT
,CompanyContractID INTEGER
,LocationID INTEGER
,CONSTRAINT pk_ContractLocations
PRIMARY KEY (ContractLocationID)
,CONSTRAINT fk_ContractLocations_CompanyContracts
FOREIGN KEY (CompanyContractID)
REFERENCES CompanyContracts (CompanyContractID)
,CONSTRAINT fk_ContractLocations_Locations
FOREIGN KEY (LocationID)
REFERENCES Locations (LocationID)
)

Open the Relationships window, and use "Show All".


Sincerely,

Chris O.

On Jun 5, 3:14 am, "Chris2"
wrote:
CompanyName TEXT(96)

I'm interested in how you came up with 96.

FWIW in my jurisdiction (UK) it is was previously VARCHAR(70), now
NVARCHAR(255):

http://www.govtalk.gov.uk/gdsc/html/...-0-Release.htm

though I have yet to encounter a corporate body with a non-ascii name.

LocationName TEXT(96)

I'm interested in how you came up with 96...

Jamie.

--

Wow, thanks for all the responses so far. It might take a little while to
digest some of this.

For both responses so far, I have the same question.

Would I be able to pull up a contract showing the Company Name, Contract
Number, and Specific location, plus show all of the other contracts that are
associated with that contract?

For a group of associated contracts, I want to be able to pull up a contract
and also show all of the other contracts that are associated with that one.
Whether there be a main contract or not.

I think I am having trouble visualizing it because it is the same field.
ContractID. I understand the relationship between Contract and Location.
Two separate tables with a one to many relationship between the two. I just
can't figure out the first case.

Both responses so far are going to give me something to test out and see. I
appreciate all of the thought that has gone into the responses.

Thanks again,
Lee (cableguy)

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200706/1

"cableguy47905 via AccessMonster.com" u4349@uwe wrote in message
news:7342e047b00e4@uwe...
Wow, thanks for all the responses so far. It might take a little
while to
digest some of this.

For both responses so far, I have the same question.

Would I be able to pull up a contract showing the Company Name,
Contract
Number, and Specific location, plus show all of the other contracts
that are
associated with that contract?

Lee,

Although you did mention "but I don't know how to relate the contracts
to each other.", you did not describe what this meant.

I had assumed contracts were associated with each other by the company
they were associated with. (If this is so, the answer to your
question above is yes, it's possible. It would be a form/subform
arrangement for display, where the subform showed "all the other
contracts".)

In what way are your contracts associated to each other in your
business?



For a group of associated contracts, I want to be able to pull up a
contract

Based on what? Is the choice of contract that is pulled up from the
group of associated contracts random? Is the user inputting this
choice in a text box (or a combo box)? (As above, what defines a
"group of associated contracts"?)


and also show all of the other contracts that are associated with
that one.
Whether there be a main contract or not.

I thought every company always had a main contract. Hmm, a careful
reading of your original post shows that this is not necessarily the
case, but fortunately it does not invalidate the design I wrote out.

What defines a "main contract" and whether a company has one, or not?



Both responses so far are going to give me something to test out and
see. I
appreciate all of the thought that has gone into the responses.


You're welcome.


Sincerely,

Chris O.

"Jamie Collins" wrote in message
oups.com...
On Jun 5, 3:14 am, "Chris2"
wrote:
CompanyName TEXT(96)

I'm interested in how you came up with 96.

FWIW in my jurisdiction (UK) it is was previously VARCHAR(70), now
NVARCHAR(255):


http://www.govtalk.gov.uk/gdsc/html/...-0-Release.htm

though I have yet to encounter a corporate body with a non-ascii
name.

LocationName TEXT(96)

I'm interested in how you came up with 96...

Jamie.



Jamie,

I hope I'm not too great a disappointment when I say that they were
basically random numbers I yanked out of the air when writing up the
DDL SQL.

I have not previously considered adhering to known ANSI, ISO, or
governmental standards on the lengths of certain types of attributes
when responding on an ad hoc help basis in this newsgroup.

Sincerely,

Chris O.

I'm not clear about "contacts that are associated with that contract."

The core idea is that a contract can have several detail lines (covering
different aspects, e.g. different locations), like an order has multiple
details lines. For an example of that, open the Northwind sample database
that installs with Access, and see the relation between Orders and Order
Details.

Is that what you mean? Or are you talking about something else, such as the
a contract that is derived from another one (such as a renewal that
effectively duplicates a previous one for a different timeframe)?

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"cableguy47905 via AccessMonster.com" u4349@uwe wrote in message
news:7342e047b00e4@uwe...
Wow, thanks for all the responses so far. It might take a little while to
digest some of this.

For both responses so far, I have the same question.

Would I be able to pull up a contract showing the Company Name, Contract
Number, and Specific location, plus show all of the other contracts that
are
associated with that contract?

For a group of associated contracts, I want to be able to pull up a
contract
and also show all of the other contracts that are associated with that
one.
Whether there be a main contract or not.

I think I am having trouble visualizing it because it is the same field.
ContractID. I understand the relationship between Contract and Location.
Two separate tables with a one to many relationship between the two. I
just
can't figure out the first case.

Both responses so far are going to give me something to test out and see.
I
appreciate all of the thought that has gone into the responses.

Thanks again,
Lee (cableguy)

Thanks for keeping up with me on this. i really appreciate it.


Although you did mention "but I don't know how to relate the contracts
to each other.", you did not describe what this meant.

A company may have one contract with us for specific purpose, and another for
a different purpose, etc. They are able to be active all at the same time,
so there isn't a timeframe, or a renewal of a contract to worry about. So
all of these contracts have one thing in common, the company.

Sometimes a contract may have different Locations associated with it. So a
contract can be representative of several different locations with all their
own personal information such as address, phone, email etc. Those locations
are only associated with the one contract, not with any others that the
company has with us.



I had assumed contracts were associated with each other by the company
they were associated with. (If this is so, the answer to your
question above is yes, it's possible. It would be a form/subform
arrangement for display, where the subform showed "all the other
contracts".)

Yes, I am pretty sure I understand that part, but I don't know how the tables
in the background are going to be set up. Is it possible to update all of
this information on one form? Maybe with subforms is alright.

Right now the DB has just one table and all of the data is able to be updated
via one form. There is no normalization to it at all. It has worked fine up
til now. I want to be able to perform more specific searches/queries. So it
doesn't work for me right now.

For a group of associated contracts, I want to be able to pull up a contract

Based on what? Is the choice of contract that is pulled up from the
group of associated contracts random? Is the user inputting this
choice in a text box (or a combo box)? (As above, what defines a
"group of associated contracts"?)

This would be by simply doing a "find" on the txtContractID textbox.
Right now it brings up all the information, but it is just not functional.
You have to put in the main contract to find out the others associated with
it.



What defines a "main contract" and whether a company has one, or not?

Nothing really defines it, it just happens to be the one that gets the most
attention. I could be any one of them. No real definition for it.

I did create the tables that you gave me for the design that you had in mind,
but I am a little stumped about the ContractLocations table. I am not sure
what that table is for. Couldn't I just put the LocationID in the
CompanyContract table and do a one to one with the Location table?

Thanks again for the help.

--
Message posted via http://www.accessmonster.com

"cableguy47905 via AccessMonster.com" u4349@uwe wrote in message
news:734da044a9023@uwe...
Thanks for keeping up with me on this. i really appreciate it.


You're welcome.



Although you did mention "but I don't know how to relate the
contracts
to each other.", you did not describe what this meant.

A company may have one contract with us for specific purpose, and
another for
a different purpose, etc. They are able to be active all at the
same time,
so there isn't a timeframe, or a renewal of a contract to worry
about. So
all of these contracts have one thing in common, the company.

Sometimes a contract may have different Locations associated with
it. So a
contract can be representative of several different locations with
all their
own personal information such as address, phone, email etc. Those
locations
are only associated with the one contract, not with any others that
the
company has with us.


Thank you for clarifying.

It may be that the relationship between Locations and
ContractLocations that I specificed would have to be altered, since
contracts can have many locations, but each location can only have one
contract.

Each contract may have many locations. (1-to-Many from Contracts to
ContractLocations.)

Each location will appear on only one contract. (1-to-1 from Locations
to Contract Locations.)





I had assumed contracts were associated with each other by the
company
they were associated with. (If this is so, the answer to your
question above is yes, it's possible. It would be a form/subform
arrangement for display, where the subform showed "all the other
contracts".)

Yes, I am pretty sure I understand that part, but I don't know how
the tables
in the background are going to be set up.

The tables would be set almost up exactly as I gave them in my earlier
post.

I have modified them per these clarifications.

CREATE TABLE Companies
(CompanyID AUTOINCREMENT
,CompanyName TEXT(255)
,CONSTRAINT pk_Companies
PRIMARY KEY (CompanyID)
)

CREATE TABLE Contracts
(ContractID AUTOINCREMENT
,ContractName TEXT(255)
,CONSTRAINT pk_Contracts
PRIMARY KEY (ContractID)
)

CREATE TABLE Locations
(LocationID AUTOINCREMENT
,LocationName TEXT(255)
,CONSTRAINT pk_Locations
PRIMARY KEY (LocationID)
)

CREATE TABLE CompanyContracts
(CompanyContractID AUTOINCREMENT
,CompanyID INTEGER
,ContractID INTEGER
,CONSTRAINT pk_CompanyContracts
PRIMARY KEY (CompanyContractID)
,CONSTRAINT fk_CompanyContracts_Companies
FOREIGN KEY (CompanyID)
REFERENCES Companies (CompanyID)
,CONSTRAINT fk_CompanyContracts_Contracts
FOREIGN KEY (ContractID)
REFERENCES Contracts (ContractID)
,CONSTRAINT un_CompanyID_ContractID
UNIQUE (CompanyID, ContractID)
)

CREATE TABLE MainContracts
(CompanyContractID INTEGER
,CONSTRAINT pk_MainContracts
PRIMARY KEY (CompanyContractID)
,CONSTRAINT fk_MainContracts_CompanyContracts
FOREIGN KEY (CompanyContractID)
REFERENCES CompanyContracts (CompanyContractID)
)


CREATE TABLE ContractLocations
(ContractLocationID AUTOINCREMENT
,CompanyContractID INTEGER
,LocationID INTEGER
,CONSTRAINT pk_ContractLocations
PRIMARY KEY (ContractLocationID)
,CONSTRAINT fk_ContractLocations_CompanyContracts
FOREIGN KEY (CompanyContractID)
REFERENCES CompanyContracts (CompanyContractID)
,CONSTRAINT fk_ContractLocations_Locations
FOREIGN KEY (LocationID)
REFERENCES Locations (LocationID)
,CONSTRAINT un_LocationID
UNIQUE (LocationID)
)


Is it possible to update all of this information on one form? Maybe
with subforms is alright.

I would like to say yes and yes, but I am running out the door in 4
minutes and don't have time to create and load sample data to the
tables, and then design a test form and write a step-by-step to
replicate what I did.

If I have time, I'll be back on it later tonight, and for sure when I
get home from work tomorrow evening (07-Jun-2007).



Right now the DB has just one table and all of the data is able to
be updated
via one form. There is no normalization to it at all. It has
worked fine up
til now. I want to be able to perform more specific
searches/queries. So it
doesn't work for me right now.

For a group of associated contracts, I want to be able to pull up
a contract

Based on what? Is the choice of contract that is pulled up from
the
group of associated contracts random? Is the user inputting this
choice in a text box (or a combo box)? (As above, what defines a
"group of associated contracts"?)

This would be by simply doing a "find" on the txtContractID textbox.
Right now it brings up all the information, but it is just not
functional.
You have to put in the main contract to find out the others
associated with
it.

I'm not sure why it would be processed that way.

In the setup I gave, once you know what one ContractID is, you know
the CompanyID, and therefore can automatically find all the associated
contracts (in the CompanyContracts table). The main contract is a
fact recorded in another table, and is only queried when it is
desirable to know what the main contract is.




What defines a "main contract" and whether a company has one, or
not?

Nothing really defines it, it just happens to be the one that gets
the most
attention. I could be any one of them. No real definition for it.

That's a minor red-flag (at least to me). It's a "fuzzy" idea
floating around in the database.

It effectively means that no one knows for certain why a certain fact
is being recorded in the database.



I did create the tables that you gave me for the design that you had
in mind,
but I am a little stumped about the ContractLocations table. I am
not sure
what that table is for. Couldn't I just put the LocationID in the
CompanyContract table and do a one to one with the Location table?

No, that won't work. It would mean that each ContractID could only be
associated with one LocationID.

Well, now that I know that the relationship between Contracts and
Locations is not many-to-many (which I why I set it up that way), yes,
you could do it that way.

----

You could dump the date columns from CompanyContracts since you have
stated you do not need them.

-----

I, personally, would keep them anyway. I would have the application
record the date the the row in CompanyContracts was entered, and then
I would also have the application record the date the contract was
closed. CompanyContract rows with a closing date would be filtered
out of most active queries so that they would not appear on forms, but
you would retain the historical proceedings of your contracts.

Or, at least you could create a set of duplicate "archive" tables to
stick deleted information in.

Someday a manager is going to ask "what did we have before".


Thanks again for the help.





Sincerely,

Chris O.