Re: What databases have taught me

> > It depends. If we imagine the same query existing in two places
> > in the code, and imagine what could make us want to change it
> > in the future, do we imagine that we'd necessarily change *both*
> > together or might we just want to change one?
>
> I'm wondering how this argument goes if we replace "two" by "ten" or
> "one hundred".
>
> What is it that caused us to need the same query to appear at two
> different places - and how likely is it that the same forces will
> require that we have the same query in three places, then maybe four,
> etc. If we do have four or more instances of the same query, do we more
> easily imagine wanting to change all of them?

If you have identical SQL statements all over the application, it is likely that you have duplication on another level, or that the SQL statements are not tailored to the specific problem in hand. But I agree that if you have, putting them inside a function might be a good idea, unless they are at the same level of complexity as calling the function.

> select name from employees where today()-birthday > X and city Y
>
> Wouldn't you say that the calculation "today() - birthday" calls for
> abstraction ? It really means "age", so the code should say "age". The
> means of abstraction doesn't have to be a method - a view would work in
> the above.

Correct, a view or stored procedure would have some benifits. It could look like this:
select name from employees where age(birthday) > X ....

> I would want to ask, too, what the condition "older than X and living in
> Y" means. It seems likely that it stands for something more abstract:
> say, "the employee is eligible for a particular healtcare plan".

Correct, another view would be a good solution.

> If that were the case, the answer to your original question would be
> "Change Both", because when the rules for eligibility changed, the
> change would need to apply to all places in the code where we compute a
> list of employees based on eligibility for that plan.

But if a view was used, that problem would not exists.

> How would you introduce that kind of abstraction using SQL?

Given tables that look like this:
employee(employeeid, birthday, ...)
healthcare_plan(planid, min_age, ...)

We could create a view
create view employee_age as
select employeeid,
datediff(curdate(), birthday) / 365 as age from employee

and another view

create view employee_healthcare_plan as
select
employeeid,
planid
from employee_age, healthcare_plan
where age >= min_age

Fredrik Bertilsson
http://frebe.php0h.com Received on Wed Jul 05 2006 - 20:24:29 CEST