patternModerate
Is a surrogate key better than a natural key in this case
Viewed 0 times
thiscasethanbetternaturalsurrogatekey
Problem
I copied this code from here:
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:
Now in this case wouldn't a surrogate key be better of identification? I mean performance wise indexing an
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
userstable (not shown here) usesemailas primary key of typetext..
- The
recordstable uses a composite key oftext+timestamp.
- The
samplestable uses a composite key of 3 fields of typetext+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
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.