Indexes and Logical design

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.

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. 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. 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.

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.

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

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 for me. Otherwise, what's the point? Received on Sat Sep 10 2005 - 18:50:29 CEST