HiveBrain v1.2.0
Get Started
← Back to all entries
patternModerate

Is a surrogate key better than a natural key in this case

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
thiscasethanbetternaturalsurrogatekey

Problem

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:

  • The users table (not shown here) uses email as primary key of type text..



  • The records table uses a composite key of text + timestamp.



  • 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?

Solution

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

Context

StackExchange Database Administrators Q#31511, answer score: 11

Revisions (0)

No revisions yet.