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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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
>

Browse pgsql-novice by date

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