>Well sorry for the long winded post, but I was wondering how you guys feel on this subject. Items like this, if not designed correctly can cause BIG headaches in the future. Of course there are always exceptions to the rules, your mileage will vary.

Very interesting post Chad, something I've hashed and rehashed many times for the environment I develop in. I would be interested in hearing how you would apply this theory of non data bearing keys to my scenario...

The short of it is I deal with mass amounts of transient data. The data comes into my apps via text files from 7 unix-based monolithic and archaic billing systems. In it's natural state, the data is rarely normalized and has no clean primary key values (with one small exception). I take the data and combine it, clean it, purge it, relate it, etc... to either normalize/denormalize end result tables that make my reporting and analysis tasks run in most efficient manner. My implementation of all this work is I use multi-field PK's and FK's because of the overhead involved with generating my own single field keys every time I dump out the exisiting data to bring in the new stuff (which happens daily/weekly/bi-weekly/monthly depending on any given source table). Also worth noting here, my apps currently manage a total of 224 tables with 20+ gb of data hanging out on my server until the next midmonth/monthend jobs run.

So what's you opinion... is this an exception or would you still stick to this non data PK rule you discussed? TIA for the input!