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

Question

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?

Answer 1

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

Answer 2

I would consider two factors:

• Primary key values should not be subject to change or reuse. Email addresses tend to be subject to change. I generally use a surrogate for user ids in databases.
• Long strings tend to disrupt index key compression when they are not the first field in the index. Depending on how the data is to be aggregated, this may be fixed by moving the email address to be the first field in the index.

Using a surrogate key which better represents the concept represented by the email address is likely a better solution. Perhaps a field like contributer_id might be a better field. An additional table translating email addresses to this field may be required.

EDIT: I have taken a second look at your design. You may want to look at modeling sampling events (location, and time taken), samples, and email addresses. Samples would be a child of sampling events. A surrogate key on sampling events might be appropriate on the sampling events table to limit the number of columns in the key when it is migrated to the child table.

I don't know what you are sampling and how it is being aggregated. How the data needs to be aggregated should be considered in the design.

Answer 3

HLGEM and BillThor both make excellent points. I would add that in addition to thinking about the stability of the key attributes and the efficiency of the key fields for index storage, there is one other factor to consider.

There is a trade-off that could impact performance when you are looking at your primary key fields. Depending on how you define your key and how fast you add data, you might end up with a hot spot that slows you down.

For example, if you use an auto-increment integer surrogate key, very high transaction rates can result in contention for the active page of data. This could limit the rate at which new data can be inserted.

On the other hand, if you use a natural key that has widely distributed values, then you need to make sure that you use a fill factor that leaves enough space for inserts. If you have a fill factor of 100% then it effectively turns your whole table into a hot spot since the DBMS will have to move a bunch of rows to make room for an insert.