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?
imagedata types will be removed in a future version of SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use
varbinary(max)instead.. And for SQL Server this wouldn't work - you cannot use a
TEXTcolumn in an index - an index entry can be 900 bytes at most