Tables with one field (design question)

[Ryan Mortier]

I was just wondering what the concensus was on tables that have one field and that field is the primary key.

Example:

Users
-----------------------------------
id | username | password | roles_id
-----------------------------------

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?

[itmitică]

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]

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

[itmitică]

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]

And most importantly, for no: when you change something in the roles names in the roles table, it won't be automatically reflected in users table.

yawn... ON UPDATE CASCADE

[itmitică]

yawn... ON UPDATE CASCADE

yawn... it works, for kiddies databases.


<hr>


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]

If we're at amateur hour, he could just put 1, 2, 3, ..., 13 in the roles_id column ...

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]

I'll have to do some more reading. Thanks to both of you for pointing out the pros and cons for both methods.

[itmitică]

now you're just being childishly churlish

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.