Re: Database design question

Neil Truby <ntruby@netcomuk.co.uk> wrote in article <329854CA.45D@netcomuk.co.uk>... > say I have a table with a million rows. One column is an indicator, > which will almost always be null. However, it is *very* important to be > able to quicly identify the 5 rows (out of the million) that have the > indicator set. Some DBMSs, Adabas for example, would allow the column > to be set up as a "null-suppressed index", that is, only non-null values > would have an index entry built for them. This is extremely efficient. > > How would you do it in a relational design? In the absence of all of the "null supressed" and "bit map" index features which others have mentioned, a good general relational design would be to have a separate table listing the primary keys of the rows you wanted to flag. Finding the flagged rows would then be a smiple matter of joining to the "flagged rows" table. Flagging a row would involve inserting into the flagged rows table, and unflagging a row would involve deleting from the flagged rows table. If the number of rows to be flagged is really small, you probably don't even need an index for it. As long as the main table is indexed by its primary key, a query which joins the flagged rows table to the main table should be very quick. -- Irwin Goldstein Objective Software Systems, Inc. http://www.objectsoft.com