When to use an auto-increment key?

Question

I've recently started to work on MySQL and while I've read some documentation on database structure, I cannot get my head around auto-increment keys and why using them.

I have been told:

• it's best to use a number instead of text as a primary key,
• it's best to use a key that doesn't have any business signification

Let's look at the situation below:

 tStores        tSales         tCustomers
----------    -----------    --------------
store_id       sale_id        customer_id
storeCode      store_id
               customer_id

First, I load some data in tStores for all the stores products can be sold. In our business, all stores have a 4 letters code to identify them. I could use this as a primary key, but based on the recommendations above I should use a store_id field that auto-increments?

The problem is, each time I insert something in tSales, I have to go back to tStores and do something like:

SELECT store_id from tStores WHERE storeCode = @myStoreCode;

Assuming I am loading hundreds of thousands rows in tSales for each store, would it not be more efficient to use the storeCode as primary key? What would be the most efficient way to deal with this?

Answer 1

Yes you can use storeCode as the primary key, it will work if you can ensure it is unique. Then you will add a foreign key on your other tables to establish the relationship.

The benefit of auto increment index are:

• It is usually faster than any index on other column type
• It is usually recommended by some framework (such as Laravel in PHP)

Related to you structure I would comment on some points:

• You have mixed casing columns/tables. When working on MySQL, especially when used on different OS (Windows/Linux), I would always recommend to use lowercase names for both schemas, tables and columns.
• You added a prefix in front of store_id and store_code. This prefix is not necessary. Why not simply naming the columns id and code.
• The relationship on tSales should be named tStores_id instead to clearly indicate from which table and which column you are referring to.

Here the SQL code for this example:

CREATE SCHEMA `myshop` ;

CREATE TABLE `store`.`stores` (
  `code` VARCHAR(10) NOT NULL,
  PRIMARY KEY (`code`));

CREATE TABLE `store`.`sales` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `store_code` VARCHAR(10) NOT NULL,
  `customer_id` INT NOT NULL,
  PRIMARY KEY (`id`));

CREATE TABLE `store`.`customers` (
  `id` INT NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`));

ALTER TABLE `store`.`sales` 
ADD INDEX `fk_sales_customers_id_idx` (`customer_id` ASC) VISIBLE;

ALTER TABLE `store`.`sales` 
ADD CONSTRAINT `fk_sales_customers_id`
  FOREIGN KEY (`customer_id`)
  REFERENCES `store`.`customers` (`id`)
  ON DELETE CASCADE
  ON UPDATE CASCADE;

ALTER TABLE `store`.`sales` 
ADD INDEX `fk_sales_stores_code_idx` (`store_code` ASC) VISIBLE;

ALTER TABLE `store`.`sales` 
ADD CONSTRAINT `fk_sales_stores_code_id`
  FOREIGN KEY (`store_code`)
  REFERENCES `store`.`stores` (`code`)
  ON DELETE CASCADE
  ON UPDATE CASCADE;