Hi, Chad. How was TechEd?

Funny you should post this now. I was just in xCase trying to decide how to deal with this issue. I decided in this case to use natural keys for some simple lookup tables in the application I'm designing. I did this because they'll be doing a lot of ad-hoc reporting and I wanted to make it as simple as possible. In cases where I thought there might be changes to the structure or where more attributes could be added to the lookup table I stuck with artificial keys.

I always say that if you fully understand a rule, you're qualified to break it. In other words, if you understand the pitfalls of natural keys you can make informed decisions on when to use them.

Will you be at the SQL Server UG tomorrow. I'm going to try to go. Maybe I'll see you there.

>I recently got into a discussion with a co-worker about using non-data bearing Primary Keys (unique ID's or identities in SQL/Access terms). My feeling is this:
>
> ALL tables should have a non-data bearing PK. Even simple lookup tables (ie a states table). Of course there are always exceptions, but I think non-data bearing PK's should be the rule NOT the exception.
>
>Benefits:
>1) Non-data bearing keys give you the flexibility to change your structures in the future (I know nobody does this <g>). If I decide to changes my two place state abbreviation to the full state name I only have to change one table. All related tables are still OK.
>
>2) If your natural key has multiple fields you do not need to migrate multiple field to other tables.
>
>3) It makes ER diagrams and databases easier to read. If you use a simple naming convention you know any field with a FK (my convention) a the end is the one and only foreign key into the table for another table.
>
>Disadvantages:
>1) You need an extra index on the natural key. I don't see this as a problem with Fox indexes and with SQL 7.0 coming out this will no longer be a problem. (there are no other databases out there, right?<g>)
>
>2) Joins may be more complex. I think this only applies to simple lookup tables though. Any tables with multi-field PK's, a non-data bearing key makes them easier.
>
>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.