One table or 2?

I need to track contracts our company has with other
companies. I thought I would create a table that would
contain CompanyInfo such as CompanyName,Address,Phone,
etc. and another table with ContractInfo:CompanyName,
ContractType, EffectiveDate, ExpirationDate, etc. But now
I found out that the Address,Phone, etc can be different
for each contract. Could I could have a single table or
should I still keep the company table for CompanyName just
to have consistency in data entry, etc. What's wrong with
this picture, I feel like I'm missing something.

On Thu, 8 Jul 2004 13:10:41 -0700, "FG"
wrote:

I need to track contracts our company has with other
companies. I thought I would create a table that would
contain CompanyInfo such as CompanyName,Address,Phone,
etc. and another table with ContractInfo:CompanyName,
ContractType, EffectiveDate, ExpirationDate, etc. But now
I found out that the Address,Phone, etc can be different
for each contract. Could I could have a single table or
should I still keep the company table for CompanyName just
to have consistency in data entry, etc. What's wrong with
this picture, I feel like I'm missing something.

Neither one NOR two: you need at least FOUR, I'm guessing!

CompanyInfo
CompanyName *only* if you can be sure it's unique; you may want an
autonumber CompanyID as the Primary Key instead
other info about the company as a whole

Contracts
ContractID a unique contract number, manually assigned,
automatically assigned, or perhaps an Autonumber
type, dates, etc.

Addresses
AddressID autonumber primary key
StreetNumber
StreetName
Suffix e.g. Ave., Ct., St.
City
State you'll probably want a table of states
PostCode
Country and of countries

ContractAddresses
ContractID link to Contracts
AddressID link to Addresses


John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public

If you only have one contact per company, you could get away with just one large table, with one field for each thing (Compay name, company address, contract name, contract address, etc.)

But if you might have more than one contact for each company, two tables would work. The first table would contain just company info (name, address, whatever else), and a primary key field (such as the company's tax id#, or maybe an autonumber) and it would have a one-to-many link with a second table that had the contact info (name, job title, address, etc.).

-diana


"FG" wrote:

I need to track contracts our company has with other
companies. I thought I would create a table that would
contain CompanyInfo such as CompanyName,Address,Phone,
etc. and another table with ContractInfo:CompanyName,
ContractType, EffectiveDate, ExpirationDate, etc. But now
I found out that the Address,Phone, etc can be different
for each contract. Could I could have a single table or
should I still keep the company table for CompanyName just
to have consistency in data entry, etc. What's wrong with
this picture, I feel like I'm missing something.