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
>
