Re: where clauses with and

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
>