Table with only one column or add a numeric primary key?
database, database-design, mysql, schema
Solution
This boils down to the surrogate key versus natural key debate in the database world. See for example here, here and here for texts on the topic. I think both choices are valid, but in this case I would choose the `AccountID` as a natural key (given that the `AccountID` is unique for each account, will not be null, and will not be subject to changes), because it means less overhead. In this case, I do not see added value to a surrogate key.
Natural keys:
- have meaning for the user
- are hard to change when needed
- may lead to needing less joins in queries
Surrogate keys:
- don't mean anything to the user
- are not subject to changes
- may lead to needing more joins in queries
- may require extra or larger indexes
Problem
Let's say I need a simple table with account id and no other information. There are two ways to do it: ``` id varchar(255) PRIMARY KEY ``` Or to add a numeric primary key: ``` id int PRIMARY KEY accountId varchar(255) UNIQUE NOT NULL ``` What are the advantages / disadvantages of both approaches and which one would you choose and why? What implications does the first solution has to maintainability (what if we need to change the id for a single row) and for performance?