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:
userstable (not shown here) uses
recordstable uses a composite key of
samplestable uses a composite key of 3 fields of type
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?