Mike,

>>Nowerdays I use a lot of surrogates, but when I see more use in an intelligent key, I'll use that one.
>
>Just out of curiousity, say you have an address table, it has these fields:
>
>

firstname
>lastname
>address1
>address2
>city
>state
>zip

>
>What expression would you use as the Intelligent Key?

Non of those. I think your example just shows a general misconception about intelligent keys. In your example I might choose a surrogate key or like in the navision database make a visible (and thus editable and therefore intelligent) ID field in which the keys can be generated. In fact in the navision database you can define so called number ranges. For example, for persons you could define the numberrange is from "PRSN000001" to "PRSN999999" and it will give you a warning when it reaches "PRSN999900"

Those keys, though ussually generated are editable and therefore intelligent. In the Navision database a Primary key always is a single column (so you don't have to deal with composite keys). You're free to use number ranges to automaticly generate values, or type them mannually (e.g. Itemno, Customerno). In certain cases (in child grids for example.), You're also able to hide the primary key (In fact by definition it becomes a Surrogate key, though it always can be changed by make it visible again).

Another example. In an invoice table, I could directly use the invoicenumber as a Primary key. Since the domain describes that an invoicenumber never should be changed, I see no objection to use it directly as a primary key. Adding just another column for a surrogate PK would be just overhead and worst of all, confusing and misleading when debugging your system when looking into child tables.

In an article table, when the domain describes it should be stable or might only be changed under strict circumstances (via cascading updates). You might enjoy the choice to use this as an primary key because debugging your system is much easier as the articlenr directly shows up in child tables. Also performance in lot's of queries might be better because of less joins.

Bottom line: In some cases where there is no other suitable candidate for a primary key, create a new primary key which only use is to identify the record. As shown in the navision database this can be both a surrogate or intelligent key. In other cases where there is a suitable PK available (depends on the domain), use that one (Examples: Invoicenumber, ItemNo, CustomerNo) because it will make you life easier when building, maintaining and debugging your system and keeps performance to the max as it generally needs less joins in queries.

Walter,