title: SQL Database Design Best Practice (Addresses)
body: <p>Of course I realize that there's no one "right way" to design a SQL database, but I wanted to get some opinions on what is better or worse in my particular scenario.</p>
  
  <p>Currently, I'm designing an order entry module (Windows .NET 4.0 application with SQL Server 2008) and I'm torn between two design decisions when it comes to data that can be applied in more than one spot.  In this question I'll refer specifically to Addresses.</p>
  
  <p>Addresses can be used by a variety of objects (orders, customers, employees, shipments, etc..) and they almost always contain the same data (Address1/2/3, City, State, Postal Code, Country, etc).  I was originally going to include each of these fields as a column in each of the related tables (e.g. Orders will contain Address1/2/3, City, State, etc.. and Customers will also contain this same column layout).  But a part of me wants to apply DRY/Normalization principles to this scenario, i.e. have a table called "Addresses" which is referenced via Foreign Key in the appropriate table.</p>
  
  <pre><code>CREATE TABLE DB.dbo.Addresses
      (
          Id          INT
                      NOT NULL
                      IDENTITY(1, 1)
                      PRIMARY KEY
                      CHECK (Id &gt; 0),
  
          Address1    VARCHAR(120)
                                  NOT NULL,
  
          Address2    VARCHAR(120),
  
          Address3    VARCHAR(120),
  
          City        VARCHAR(100)
                      NOT NULL,
  
          State       CHAR(2)
                      NOT NULL,
  
          Country     CHAR(2)
                      NOT NULL,
  
          PostalCode  VARCHAR(16)
                      NOT NULL
      )
  
  CREATE TABLE DB.dbo.Orders
      (
          Id          INT
                      NOT NULL
                      IDENTITY(1000, 1)
                      PRIMARY KEY
                      CHECK (Id &gt; 1000),
  
          Address     INT
                      CONSTRAINT fk_Orders_Address
                      FOREIGN KEY REFERENCES Addresses(Id)
                      CHECK (Address &gt; 0)
                      NOT NULL,
  
          -- other columns....
      )
  
  CREATE TABLE DB.dbo.Customers
      (
          Id          INT
                      NOT NULL
                      IDENTITY(1000, 1)
                      PRIMARY KEY
                      CHECK (Id &gt; 1000),
  
          Address     INT
                      CONSTRAINT fk_Customers_Address
                      FOREIGN KEY REFERENCES Addresses(Id)
                      CHECK (Address &gt; 0)
                      NOT NULL,
  
          -- other columns....
      )
  </code></pre>
  
  <p>From a design standpoint I like this approach because it creates a standard address format that is easily changeable, i.e. if I ever needed to add Address4 I would just add it in one place rather than to every table.  However, I can see the number of JOINs required to build queries might get a little insane.</p>
  
  <p>I guess I'm just wondering if any enterprise-level SQL architects out there have ever used this approach successfully, or if the number of JOINs that this creates would create a performance issue?</p>