One table or 2?

  • Thread starter Thread starter FG
  • Start date Start date
F

FG

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.
 
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.
Click to expand...

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
 
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
 
Back
Top