Re: MS Northwind DB Theory

stu wrote:

> Would it be a good idea to take the address info out of all the tables and
> make a new address table? This table could be given a surrogate primary
> key.
>
> This would cause circular references in th ER diagram. Ive been told this
> is a no no. Why?

I store addresses in a separate table. This lets me keep all my addresses in one table.

I use an association table (better term?) to relate addresses with table that might otherwise have address columns in their definition.

There are no circular references this way.

Here is example DDL:

CREATE TABLE Address
 (address_id INTEGER NOT NULL,
  street VARCHAR(32),
  city VARCHAR(32),
  state VARCHAR (2),
  zip VARCHAR(9),
  is_primary BOOLEAN, -- This isn't SQL-92 is it?   address_type VARCHAR(32), -- 'business', 'home', 'billing', etc.   PRIMARY KEY (address_id));

These are entities that have addresses:

CREATE TABLE Firm
 (firm_name VARCHAR(32) NOT NULL,
  PRIMARY KEY (name));

CREATE TABLE Person
 (person_id INTEGER NOT NULL,
  first_name VARCHAR(32),
  last_name VARCHAR(32) NOT NULL,
  PRIMARY KEY (person_id));

This is how they relate:

CREATE TABLE Firm_Address
 (firm_name VARCHAR(32) NOT NULL REFERENCES (Firm),   address_id INTEGER NOT NULL REFERENCES (Address),   PRIMARY KEY (firm_name, address_id));

CREATE TABLE Person_Address
 (person_id INTEGER NOT NULL REFERENCES (Person),   address_id INTEGER NOT NULL REFERENCES (Address),   PRIMARY KEY (person_id, address_id));

Alan Gutierrez - ajglist_at_izzy.net Received on Fri Jan 31 2003 - 16:54:59 CET