Re: Indexes and Logical design

"David Cressey" <david.cressey_at_earthlink.net> wrote in message news:Qq9We.12635$FW1.9976_at_newsread3.news.atl.earthlink.net...
>
> "Roy Hann" <specially_at_processed.almost.meat> wrote in message
> news:UqidndyZjK3RYbrenZ2dnUVZ8qqdnZ2d_at_pipex.net...

> Due to what might be a historical accident, the model of the database
made
> visible to the programmers has been called the "logical model", in my
> experience.

Actually that is something I have been fretting about recently. In the real (i.e. SQL) world there are actually two physical models: the one the programmers are required to see (for whatever reason), and the ones they are required (or asked) to pretend they don't see.

The one they are required to see might have extra tables that were introduced when vertical fragmentation is the only solution to a performance problem. Or it might have fewer tables than the logical model, because (rightly or wrongly) the design has been denormalized (again for performance reasons). There are innumerable other reasons why extra tables are introduced, and others merged. But the tables in this model have to be known and explicitly manipulated by the SQL the programmers write, or at least be capable of it.

The other physical model (the one the programmers are supposed to ignore) is the one in which the secondary indices are exposed, and maybe the base tables that are really only meant to treated as the basis of views. The fragments of horizontally fragmented tables are perhaps also visible here. This is the DBA/system admin's view of the database. The tables in this model generally are all too visible, but the programmers would be imprudent (frankly, wrong) to explicitly manipulate them. The DBA must always be at liberty to change anything in this model that is not also in the "other" physical model, without breaking the applications.

There are clearly two different physical models.

> And, prior to my arrival on the scene, this "logical model"
> (actually political model) had generally, though not always, lumped the
> indexes in with the tables. I found it convenient (prehaps "politically
> correct") to go along.

OK so far. Or at least I choose not to argue about it.

> Besides, knowing the indexes helps the programmers
> to better queries. [snip]

This does bother me. What kind of rubbish DBMS requires the programmers to be aware of an index in order to write "better queries? Assuming we're talking about SQL here, the only way knowledge of an index can help them write better code is by involving the index explicitly in a FROM clause. So what happens when the DBA decides the index is not efficient and drops it? What happens when the optimal execution strategy would be to ignore the index, but there it is, hard-coded into the SQL?

But perhaps I am misunderstanding you. Perhaps when you say "knowing the indexes", what you mean is "knowing the alternate physical keys"? The alternate keys definitely belong in the first of my physical models above, not with the indexes in the second. By referring to one of these in the WHERE clause the programmer certainly could write better code. And if the alternate keys happen to coincide with candidate keys (as the easily could) then they already appear in the logical model. (Of course an alternate physical key need not be a candidate key--it might be deliberately ambiguous, to encourage physical clustering of related data.)

I really am not happy about programmers calling my first physical model a "logical model", but what they call it doesn't change what it is. And if they call it the wrong thing, perhaps that's because in SQL there are two physical models but only one name. They just *can't* make clear which one they mean.

Roy Received on Thu Sep 15 2005 - 10:30:48 CEST