Re: MS Northwind DB Theory

Alan Gutierrez <ajglist_at_izzy.net> wrote in message news:<n%w_9.70159$GX4.2911098_at_news2.east.cox.net>...
> 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

How about moving the is_primary and address_type columns to the Firm_Address and Person_Address Tables ?

This would allow the Address table to be more like Premises which would reflect physical properties that could be occupied by multiple Firms or Persons simultaneously and be different types simultaneously.

Barry Williams
Principal Consultant
Database Answers Received on Sat Feb 01 2003 - 01:23:18 CET