ryan_mortier — 2012-06-21T10:57:52-04:00 — #1
I was just wondering what the concensus was on tables that have one field and that field is the primary key.
id | username | password | roles_id
The id field in the roles table would be values like "Quality Control" and "Administrator". Since these values will always be unique, I could use it as the primary key rather than using an auto-increment id with a second field labelled "name".
My question is, is this good design practice?
system — 2012-06-21T11:04:58-04:00 — #2
Indexes based on numeric fields will be smaller in size.
Queries based on numeric fields and numeric field indexes are faster.
The users table would be bigger for no good reason because of the roles_id non-numeric values.
In the future the role names might change easily as to no longer be unique. Updating text values in the smaller roles table would be easier (for you and the RDBMS) and it would make more sense than updating the whole presumably larger users' table roles_id text column.
All the above say no. Maybe you have better counterarguments to not use auto-increment?
r937 — 2012-06-21T11:15:43-04:00 — #3
using a numeric foreign key in the users table to refer to the roles table means that a join will always be required to retrieve the role name, and a join is always slower than a query on a single table
searching for users with a specific role name will require not only a search of the roles table but also a search of the users table, and two searches are always slower than one
when browsing the users table (e.g. in a front end app like phpmyadmin) you will understand what the roles are if the role name is used as the foreign key
so to answer your question, ryan, yes, it's good practice
declaring auto_increment surrogate keys indiscriminately all over da place is bad practice
discriminate, and do it only when it's warranted
system — 2012-06-21T11:27:39-04:00 — #4
And most importantly, for no: when you change something in the roles names in the roles table (like, for example, when you misspelled a role name), it won't be automatically reflected in users table.
While with a auto-increment/sequence/anonymous key, the effective values are a relative quantity.
r937 — 2012-06-21T11:28:35-04:00 — #5
yawn... ON UPDATE CASCADE
system — 2012-06-21T11:33:18-04:00 — #6
yawn... it works, for kiddies databases.
If we're at amateur hour, he could just put 1, 2, 3, ..., 13 in the roles_id column in the users table, forget about roles table and use a php array to decode it. I wonder what happens when he loses the napkin with the user roles he got from the HR?
r937 — 2012-06-21T11:39:47-04:00 — #7
now you're just being childishly churlish
ryan, you have to make up your own mind, there's plenty of information for both the pro and con sides of using surrogate auto_increments
please try to remember to discriminate
ryan_mortier — 2012-06-21T11:45:09-04:00 — #8
I'll have to do some more reading. Thanks to both of you for pointing out the pros and cons for both methods.
system — 2012-06-21T11:49:52-04:00 — #9
No, your yawning is. I was being exceptionally civil until that moment.
Anyway, primary keys are supposed to be immutable, never changing, constant. That's the theory, anyway.
Which makes a trigger like ON ... CASCADE for a PK a bad practice. To paraphrase someone I look up to when it comes to databases:
If your design requires it -- change your design now if you can.
mittineague — 2014-09-22T19:55:48-04:00 — #10
This topic is now archived. It is frozen and cannot be changed in any way.