I wrote an article in Universal Thread Magazine, May 2002, outlining advantages of each approach.

I personally favor primary keys without business value (e.g., a serial number, or a GUID); one advantage is that the user can change "key" values (what the user sees as keys, such as client codes), without the database having to propagate those changes to scores of tables. Also, with keys that do have business value it is often difficult to find a unique combination - and combining several fields makes relations complicated. With serial numbers, you also save space. For example, an extra 4-byte field in the main table client in this example) is more than compensated by space savings in many records for each client, in several other tables, since the keys are usually shorter. (A GUID uses 16 bytes, so you may not save space - but a GUID has other advantages for distributed systems.)

While I personally dislike the other approach (using keys with business value), they do have their advantages as well, such as making debugging easier - in the "no business value" approach, you may have to do some additional lookups ("Who was client #5321, again?") Some claim that joining tables may be easier; for example, you already have the client number as a foreign key in the invoice table. But in this example, you will usually also need additional data for the client (like the full name).

>what's the right table structures for one-to-many forms?
>
>eg. for invoicing, I use:
> ...