Skip site navigation (1) Skip section navigation (2)

Re: where clauses with and

From: "paul butler" <paul(at)entropia(dot)co(dot)uk>
To: "Henshall, Stuart - Design & Print" <SHenshall(at)westcountry-design-print(dot)co(dot)uk>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: where clauses with and
Date: 2002-10-17 13:28:21
Message-ID: T5dff28430fac1785ec24c@pcow034o.blueyonder.co.uk (view raw or flat)
Thread:
Lists: pgsql-novice
I hope its normalised 
its a many to many relationship between
organisations and usertypes:

CREATE TABLE "organisations" (
	"orgname" character varying(100) NOT NULL,
	etc
	Constraint "organisations_pkey" Primary Key ("orgname")
);

CREATE TABLE "usertype" (
  "usertype" varchar(50) NOT NULL, 
  CONSTRAINT "usertype_pkey" PRIMARY KEY ("usertype")


CREATE TABLE "users" (
  "orgname" varchar(100) NOT NULL, 
  "usertype" varchar(50) NOT NULL, 
  CONSTRAINT "users_pkey" PRIMARY KEY ("orgname", 
"usertype"), 
  CONSTRAINT "<unnamed>" FOREIGN KEY ("orgname") 
REFERENCES "organisations" ("orgname") ON DELETE 
CASCADE ON UPDATE CASCADE NOT DEFERRABLE 
INITIALLY IMMEDIATE, 
  CONSTRAINT "<unnamed>" FOREIGN KEY ("usertype") 
REFERENCES "usertype" ("usertype") ON DELETE NO ACTION 
ON UPDATE CASCADE NOT DEFERRABLE INITIALLY 
IMMEDIATE
) WITH OIDS;

am I making a terrible mistake here? I am dimly aware of 
normalisation, functional dependencies, BCNF and all that. Is ther 
a better way to approach this?

Cheers

Paul


> 
> However I strongly suspect that the tables are not normalised.
> With out knowing more specific info I can't say exactly how to normalisem,
> but I'm guessing users should be split into a different table that refernces
> the organisation through some sort organtisation id.
> Also the usertype should probably be in a third table with the user table
> saying what kind of user they are and another table with org id and usertype
> id to say what type of users each organisation can handle.
> This is of course conjecture on my part. I think there are some docs about
> normalisation on techdocs.postgresl.org, if not any relational db theory
> book should handle it
> hth,
> - Stuart
> 



pgsql-novice by date

Next:From: Mattia BocciaDate: 2002-10-17 13:48:11
Subject: information
Previous:From: Jules AlbertsDate: 2002-10-17 13:21:21
Subject: Re: char() or varchar() for frequently used column

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group