"Where possible avoid simply using id as the primary identifier for the table."
Has anyone had trouble by using surrogate primary keys? I've found the opposite of what the author said could be more true: composite keys should be avoided instead.
Has anyone had trouble by using surrogate primary keys? I've found the opposite of what the author said could be more true: composite keys should be avoided instead.
Unless you're using innodb, primary key is not special, it's just another index. If you decide to use surrogate keys you also need to enforce the natural key too (if possible).
There are also some nice performance advantages you can get from natural keys with covering composite indices in the case where you only want the key. But in general a table can have multiple keys in all but the highest levels of normalisation.
ON s2.mentor_id = s1.staff_num
staff_num is the primary key
To me this isan anti pattern because: - a primary should (can) never change; - composite primary keys are only useful (save space) in very large datasets
It also conflicts with his pattern: uniform suffix _id
I suspect the love of surrogate keys is just more MySQL bullshit pretending that classic SQL design is wrong merely because it is not implemented in that particular broken database.
INNER JOIN comments USING (user_id)
INNER JOIN comments ON comments.user_id = user.user_idBut that's just playing devil's advocate. In general I think ids are fine because ORMs play nice with them and FKs are simple.