Database Design – Is a Surrogate Key Better Than a Natural Key

best practicesdatabase-designindexperformanceprimary-key

I copied this code from here:

CREATE TABLE records(
    email TEXT REFERENCES users(email),
    lat DECIMAL,
    lon DECIMAL,
    depth TEXT,
    upload_date TIMESTAMP,
    comment TEXT,
    PRIMARY KEY (upload_date,email)
);

CREATE TABLE samples(
    date_taken TIMESTAMP,
    temp DECIMAL,
    intensity DECIMAL,
    upload_date TIMESTAMP,
    email TEXT,
    PRIMARY KEY(date_taken,upload_date,email),
    FOREIGN KEY (upload_date,email) REFERENCES records(upload_date,email)
);

The first thing that caught my eyes was the use of natural composite keys as primary keys for both tables.

3 things I was able to extract from this piece of code:

  1. The users table (not shown here) uses email as primary key of type text..
  2. The records table uses a composite key of text + timestamp.
  3. The samples table uses a composite key of 3 fields of type text+ timestamp+ timestamp.

Now in this case wouldn't a surrogate key be better of identification? I mean performance wise indexing an int should be better than indexing a text? Is there something that could make a surrogate key a bad choice?

Best Answer

Email is a particularly bad choice for any PK whether composite or single. See my answer on this question on Stack Overflow for why:

https://stackoverflow.com/questions/3804108/is-email-address-a-bad-primary-key/3804174#3804174

Related Question