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

Chad --

More observations on primary keys:

1. PK's that are typed as integer are very fast -- Mac Rubel did some experimentation along this line and recently published his findings in a series of articles in FoxPro Advisor.

2. "Non-data" PK values are not likely to change and are therefore better for establishing and maintaining persistent relationships. For example: If you have a FAMILY table and a MEMBER table, and members are linked to a family by Last Name, what happens if one of the members' name changes? OTOH, if the link is done using a "non-data" PK, the link remains intact, regardless of changes to any of the data.

3. If you ever plan to upsize your VFP database to SQL Server, your job will be much easier if each of your VFP tables has a PK. I think (correct me if I'm wrong) that SQL server requires each table to have a PK.

Hope this helps -- Bill