On May 20, 3:07*pm, metaperl <metap... (AT) gmail (DOT) com> wrote:
That's why triggers and constraints were invented. Besides, who is
writing that INSERT procedure? The DBA?
That "on-the-fly" filtering might exactly be the business requirement.

How is this E-R creep??

Regarding documentation: that is a process issue. If your development
process does not include design documentation steps, you end up with
no documentation. It doesn't matter where the logic resides, or who
develops it.
Code reviews are a great step in a development process. Your earlier
comments gave the impression that there are developers and there are
those who write the INSERT procedures. Now it looks like the line is
not so sharp.

BTW even Oracle does not always follow the best practices.

Ed

Whoops! Just realised i had meant to reply to this.

On Thu, 20 May 2010, metaperl wrote:

You can do this with constraints, can't you? You can make an assertion
right there in the table definition that documents the requirement,
enforces it, and leaves the tables open for access from any direction.

I appreciate that you can do more in a stored procedure than you can in a
constraint, because of the limits on syntax available in constraints (in
some systems, at least). To recycle my product/price example, if you
wanted to modify this definition:

create table product (
product_id integer primary key
-- other fields
);

create table price (
country char(2) not null,
product_id integer not null references product,
constraint price_pk primary key (country_code, product_id),
price integer not null
);

So that you couldn't insert a product without at least one price, then
you'd want to write something like:

create table product (
product_id integer primary key
check (exists (select * from price p where p.product_id = product_id))
);

But i know you can't do that in PostgreSQL, and i suspect you can't do it
in most databases.

You can work around this with a nasty hack:

create table product (
product_id integer primary key,
example_price_country char(2) not null,
constraint product_price_fk foreign key (example_price_country, product_id) references price
);

where you explicitly name a country that will have a price, and use a
foreign key constraint to that. Like i said, nasty.

Except for the code that does it, which is perfectly accurate
documentation. Moving the selection from code into a stored procedure
doesn't improve that documentation, it just moves it out of the code,
where only programmers can read it, into the database, where only DBAs can
read it.

Also, i thought you were talking about only restricting mutation, and
letting programmers do any kind of query?

tom

--
A playwright is not the best person to talk about his own work for
the simple reason that he is often unaware of what he has written. --
Alan Bennett