title: Designing contact/company/address tables for a database
body: <p>Trying to design part of a database to hold addresses, companies and contacts. I had one design of it in which I've now got the job of 'cleaning' it due to poor design.</p>
  
  <p>Bought a copy of Joe Celko's SQL Programmer Style for reference as I'm coming from a programming angle so I ended up with...</p>
  
  <pre><code>Addresses
  street_1_adr varchar(80) primary key
  street_2_adr varchar(80)
  street_3_adr varchar(80)
  zip_code varchar(10) foreign key/primary key &gt; Regions.zip_code
  </code></pre>
  
  <p>With a check to ensure all addresses are unique to prevent duplicates.</p>
  
  <pre><code>Regions
  city varchar(80)
  region varchar(80)
  zip_code varchar(10) primary key
  country_nbr integer foreign key/primary key &gt; Countries.country_nbr
  </code></pre>
  
  <p>With a check to ensure all regions are unique to prevent duplicates.</p>
  
  <pre><code>Countries
  country_nbr integer primary key
  country_nm varchar(80)
  country_code char(3)
  </code></pre>
  
  <p>With a check to ensure that only one record exists for all the information.</p>
  
  <pre><code>Companies
  company_nm varchar(80) primary key
  street_1_adr varchar(80) foreign key &gt; Addresses.street_1_adr
  zip_code varchar(10) foreign key &gt; Addresses.zip_code
  Extra information
  </code></pre>
  
  <p>With a check to ensure that only one company with that name can exist at the address specified</p>
  
  <pre><code>Contacts
  company_nm varchar(80) primary key/foreign key &gt; Companies.company_nm
  first_nm varchar(80) primary key
  last_nm varchar(80) primary key
  Extra information
  </code></pre>
  
  <p>But this means that if I want to hook, as an example, an order onto a contact I need to do it with three fields.</p>
  
  <p>Does this look right or have I completetly missed the point?</p>