Valentin Militaru wrote:
> You can do that. But first you have to do some optimisations, like:
> add a column id(bigserial) to the departamens table, after which you
> will replace the column department with id_department in the projects
> table. It is an optimisation, as you are dealing with integer, not text.
Well, that's an argument for surrogate keys, which will invoke a
philosophical war amongst purists that I won't touch...
> After that, what do you want to achieve? When you are inserting a
> department, should the server insert 2 to 8 blank records in the
> projects table which contain the inserted department? Or do you want not
> to be able to insert a department if there aren't already 2 to 8
> projects containing that department in the projects table?
I want the database to enforce logical consistency by ensuring that if a
department exists, there are at a minimum two projects and a maximum of
eight projects associated with it. Date & Darwen attribute the
enforcement of such business requirements to database constraints.
PostgreSQL lacks database constraints, but the result can often be
achieved through triggers. But I can't figure out how to enforce
consistency without deferrable triggers and without relying on the
application to maintain consistency, which is not its job.
Mike Mascari