CODEWITHSUNDEEP
mysqlnode.jsexpress
lukechambers91
lukechambers91

Reputation: 711

Should I Use auto-increment id or unique key as for foreign key?

I have some questions about good relational database design. Given the simple MySQL database Schema below and a Nodejs Express server. For a get request for truck data given a particular customer_key.

  1. Is it better to use the customer_id or the customer_key as the Foreign Key for the truck table?
  2. Could the customer_key replace customer_id? (Are auto incremented values particularly useful for some reason?)
  3. If both customer_key and customer_id are kept what is the best way to return the data. (Is thers something better than SELECT * FROM truck WHERE customer_id = (SELECT customer_id FROM customer WHERE customer_key = 'cust123');?)

Table Schema

CREATE TABLE customer (
  customer_id INT NOT NULL AUTO_INCREMENT,
  customer_key VARCHAR(100) NOT NULL UNIQUE,
  first_name VARCHAR(100) NOT NULL,
  last_name VARCHAR(100) DEFAULT NULL,
  email VARCHAR(150) NOT NULL UNIQUE,
  date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (customer_id),
  UNIQUE KEY (customer_key)
);

CREATE TABLE truck (
  truck_id INT NOT NULL AUTO_INCREMENT,
  truck_key VARCHAR(100) NOT NULL UNIQUE,
  customer_id INT NOT NULL, # should this be customer_key
  truck_type VARCHAR(100) NOT NULL,
  date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (truck_id),
  UNIQUE KEY (truck_key),
  FOREIGN KEY (customer_id) # should this be customer_key
   REFERENCES customer(customer_id) # should this be customer_key
);

Upvotes: 0

Views: 950

Answers (2)

luisvenezian
luisvenezian

Reputation: 501

I recommend that you keep using Id as your foreign key because of the data type.
It is clearly easier to compare 1 = 1 than 'anything' = 'anything'.

Upvotes: 1

ysth
ysth

Reputation: 98398

For more efficient storage, I would use the auto-increment id. Though if your unique key were a lot shorter, I would say you should just use that as the primary key (and obviously foreign key too) and ditch the id altogether. With a 100 character unique key, I can see some point in having a separate id.

For your third question, I much prefer joins to subqueries:

select truck.*
from customer
join truck using (customer_id)
where customer_key = 'cust123';

though ideally if you are going to use an integer primary key, you would get that from whatever UI you have, not the customer_key. From your example, I do question whether you have any need for varchar keys at all; do they actually represent something outside of your model?

Upvotes: 2

Related Questions