Re: Indexes and Logical design

From: VC <>
Date: Sat, 10 Sep 2005 13:49:33 -0400
Message-ID: <>

"David Cressey" <> wrote in message news:p9EUe.9891$
>A little while ago, I remarked that I reluctantly included indexes in the
> logical design, and Marshall asked why.
> There's a great case to be made against including indexes in the logical
> design, and I suspect Marshall can make that case better than I can. Let
> me
> tell you why I lean the other way. It's more a matter of several little
> reasons than one big one.

There is a very simple argument as to why indices do not belong to the logical mode: they are nothing more but a performance tool.

> Before I start, after thinking it over, I've decided that it's more
> precise
> to say that indexes and tables (in an SQL implementation) are not the
> logical model as such. They are the reflection of the logical model in
> the
> implementation. Second, tables and indexes have physical features as
> well
> as logical features. In at least the Oracle dialect of SQL, its separated
> out as follows:
> create table FUBAR (logical features) physical features;
> create index FUBAR_IDX on table FUBAR (logical features) physical
> features;
> Now, here are my reasons:
> First, Data Architect divides objects into "schema objects" and "database
> objects". Tables and indexes are both included with the schema objects,
> along with views, procedures, and other stuff.

Well, it's just an arbitrary classification, an implementation pecularity if you will, which is not related to what indexes really are (see above).

> Database objects are things
> like tablespaces, that have no counterpart in the logical model.
> Second, programmers always expect to see the indexes in the logical
> design.
> They should really need to see them, but they have a felt need. I'm
> philosophical about it.

That's odd. Why do programmers need to even know that a specific table has indexes (unless by a programmer you mean a database programmer of course) ?

>If we're going to take away the pointers they love
> to play with, we'd better throw them a bone. Indexes fill that bill.

I do not understand the above. Are you talking about some psycological problems that can be settled if the hypothetical, non-database, programmer would know tha some table has indexes ?

> Third, the CREATE UNIQUE INDEX construct ends up preventing duplicates in
> a
> table, by placing a constraint on the index. At least that's how it
> worked
> in Rdb. Constraints on the data, whether declared or not, and whether
> enforced or not, really are part of the logical model.

You are right of course that constraints are part of the logical model. However, the unique index is not. Such index is just an implementation vehicle for a unique constraint. Besides, in Oracle (with which you are apparently familiar) , a unique constraint can be enforced via a non-unique index. This piece of trivia should make it clear that an index, either unique or non-unique, is just a trick to improve performance. One can easily imagine a unique constraint enforcement without any index whatsoever although such enforcement would be impractical.

> Fourth, the DEC Rdb/VMS command "show table" shows the indexes as part
> of
> its display.

Surely, you realize that it's just an impementation pecularity, a convenient tool for the DBA.

> That's about it. This is like the death of a thousand cuts. I just don't
> want to fight this battle.
> Of course, if one is making a logical model with no idea of implementing
> it,
> all this is extraneous. I have never done that. I'm a database
> designer,
> not a theoretician. A logical model is a prelude to database construction

Well, as a database designer, you convert your conceptual simplification of the 'real world' into a logical model and then you think how to implement this model using various tools at your disposal (indexes being one of them). Such impementation would be your physical model.

> for me. Otherwise, what's the point?
Received on Sat Sep 10 2005 - 19:49:33 CEST

Original text of this message