9. Do NOT arbitrarily assign an "id" column to a table as a primary key when other columns
are perfectly suited as a unique primary key....Good example:
CREATE TABLE accounts
( accout_id bigint NOT NULL ,I would not consider the general use of natural primary keys to be best practice.Let's assume your account_id field is used as a foreign key in a dozen other tables.1) What happens if someone mis-types the account-id?To correct that, you also need to correct the FK field in the other dozen tables.2) What happens when your company starts a new project (or buys a competitor) and all the new account numbers are alpha-numeric?
Point 9 is well-intentioned, but perhaps needs to be clarified/rephrased: Developers should not be creating production-grade tables devoid of well-defined business keys, period. That would be regardless of whether they're used as de facto primary keys or simply as unique keys.
As long as that is made clear as a foundational requirement, then developers should be allowed some leeway as to the subsequent design choice between synthetic vs natural keys. Further to the above remarks, offering some guidelines on the trade-offs would be beneficial. E.g., if natural keys are chosen as Primary, it's likely that cascading mechanisms ought to be implemented. Conversely, if synthetic keys are chosen as Primary, they must be accompanied by a legitimate Unique natural key.
- John
