Purpose of IDs in a User Table Database

Question

In a table, such as a user table, where there will only be 1 row with a for any particular user, why have an ID field, let alone as the primary key?

Answer 1

I'd answer with a question: Why do you need a table for a single row? Regarding the design of the table, any table should have a primary key. I won't discuss that as it's the baseline of any design book. Regarding the way of designing that primary key, there are two schools of thought:

• one that makes the PK as a separate column, usually self generated, like a GUID or and auto increment INT (in your case a separate uniqueidentifier column);
• one that makes the PK as a column (or set of columns) internal to the table (in your case would be a username or email or SSN, whatever makes that user unique) that uniquely identify a record.

To what line you adhere, it's only a matter of taste.

Answer 2

You could use the ID instead of the username in references, so that you can rename users and retain a proper audit trail.

If user jane.doe marries john.smith, she might want to change username to jane.smith

Generally, people are more often concerned with name changes in case of divorce, but it's also useful to correct typos, or switch john to john.smith when a company grows.

Answer 3

Not sure what you mean by a "user" table in this context but ...

You are right that a table whose constraints limit it to a single row does not actually need any key attributes. In relational database terms that would be a relation with an "empty" key - a key which consists of no attributes at all. Unfortunately no SQL DBMS supports such a thing. One possible workaround is to create some arbitrary "key" attribute, put a uniqueness constraint on it and also apply a CHECK constraint to limit the "key" to a single value. So that's a reason why you may need to create a key attribute (strictly it's a superkey, not a key) even though you don't appear to need it.

Answer 4

Are you referring to a table with a single row? Or a table with multiple rows, each uniquely identified by some string? Assuming the latter...

ID INT UNSIGNED AUTO_INCREMENT, Name VARCHAR(2000) -- There you need the ID, because Name is too big to be an index.

ID INT UNSIGNED AUTO_INCREMENT, Name VARCHAR(10) -- There is very little advantage for having the ID if Name is unique. It can be used instead.

ID INT UNSIGNED AUTO_INCREMENT, GUID VARCHAR(36) -- If this is a standard UUID/GUID, then first of all, it should be declared BINARY(36) to avoid collation overhead and *3 for utf8. Anyway, it is borderline whether the GUID should, itself, be the PRIMARY KEY (and get rid of ID).

See also, discussions on "Normalization".