[Pdx-pm] (OT) Database design guru

[Austin Schutz]

On Thu, Jan 23, 2003 at 04:47:24PM -0800, Curtis Poe wrote:
> I'm going nuts with a problem at work regarding a database normalization problem.  I've been searching high and low for an answer, but not luck so far.  On the off chance that anyone wants a conundrum to chew on:
> 
> http://www.perlmonks.org/index.pl?node_id=108949&user=Ovid
> 
> Cheers,
> Ovid


	I'm kind of a database dope, but what I've seen done in similar tables
is to have a self referencing table like:

CREATE TABLE locations (
  location_id serial not null primary key,
  parent_location_id integer,
  name varchar(50) not null,
  location_type varchar(20)
)

CREATE TABLE customers (
    customer_id serial NOT NULL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    location_id integer not null,
);

	where location_type would be something like 'state'. This also makes
it possible to have further sub locations, such as county, etc.
	As mentioned I'm kind of a database dope. I'm not really sure how
to get stuff out of there efficiently using stored procedures or similar,
but I know it's possible. If this sounds like a good storage method I could ask
one of our smart database people how they use it.

	Austin