I copied this code from here:

    email TEXT REFERENCES users(email),
    lat DECIMAL,
    lon DECIMAL,
    depth TEXT,
    upload_date TIMESTAMP,
    comment TEXT,
    PRIMARY KEY (upload_date,email)

    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?


Email is a particularly bad choice for any PK whether composite or single. See my answer on this question on Stack Overflow for why:


  • +1000 for the great post. Actually after reading the various answers I feel like that all natural keys are bad candidates. Emails, Dates, Names,...etc all have a probability for change, slim but not zero. In your opinion, is there a practical situation where using a natural key can be better than a surrogate key? coz in my humbe opinion I can't see why shouldn't I always use a surrogate key??
    – Songo
    Jan 6 '13 at 20:20
  • I don't think that dates change often. A timestamp (which may often the record's insert time) cannot change. An order date doesn't change either. Jan 7 '13 at 8:40
  • 3
    @Songo - Certain "code table" values, especially those that are determined by a standards body, can be reasonable natural keys. Think of "M" or "F" for gender, and things like currency and country codes. Otherwise, surrogate keys are very often safer and better.
    – Joel Brown
    Jan 7 '13 at 12:58
  • 1
    @ypercube Dates may not change that often, but they're also not guaranteed to be unique. Consider the problem of daylight saving time, and you'll see that there are days in some countries every year that have duplicate times. Additionally, dates (IMX) are one of the most commonly mis-keyed data items. And order dates certainly can change, if, say the customer requires the order to be dated differently for tax or budget purposes. "I can't change a date" suddenly becomes "I can't make a sale" or more realistically "I can't make a sale without cancelling and re-entering the order".
    – Bacon Bits
    Jan 7 '13 at 14:12
  • 1
    @Songo - Unless you have very specialized performance requirements, I would design for maintainability and usability rather than for performance. There are no hard and fast rules for what is going to be faster. It depends on the distribution of the data values and which values you are looking for (and how often). In the end you can often only tell by measuring with production data at production volumes. One factor that is likely to be important is that covering indexes are generally fast. Filtering by country="CA" will probably be faster than joining to a code table with an int FK.
    – Joel Brown
    Jan 7 '13 at 14:35

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.

  • +1 Thanks for the reply, but what about the dates? I think it conforms to the points you mentioned in your answer. Actually I have a feeling that all natural keys are bad candidates. Emails, Dates, Names,...etc as they all have a probability for change, slim but not zero. In your opinion, is there a practical situation where using a natural key can be better than a surrogate key? coz IMHO I can't see why shouldn't I always use a surrogate key everywhere??
    – Songo
    Jan 6 '13 at 20:29
  • Dates tend to be stable. It looks like you have a valid parent -> child -> grandchild relationship.
    – BillThor
    Jan 9 '13 at 2:00
  • @Songo: Funny that you say "I have a feeling that all natural keys are bad candidates". By definition, ALL natural keys are candidate keys. :) Jan 9 '13 at 17:24

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.

  • +1 Thanks for the answer. I have a question though about this " This could limit the rate at which new data can be inserted." Do you mean that if 10 transaction where trying to insert a row in a table they all have to wait till each transaction finishes so that each one of them could get the new auto incremented value? Is this what you mean by a hot spot?
    – Songo
    Jan 7 '13 at 13:59
  • @Songo - Yes, page locks on data and index pages will cause transactions to line up. If the lineup is long enough, you may even get timeouts. That is what a "hot spot" is.
    – Joel Brown
    Jan 7 '13 at 14:29
  • I see, so using a natural key would solve this problem because it will have to be supplied by the user himself and won't be dependent on the database. However, I don't understand what you mean by the "fill factor"?
    – Songo
    Jan 7 '13 at 14:40
  • @Songo - Various DBMS allow you to specify how much empty space to keep in your data and index files. SQL Server calls this "Fill Factor". Other DBMS could have other terminology. When you insert a record it has to go into the right place according to your clustered index. If your record goes into the middle of the table and there is some free space, then it's easy to just write the record where it needs to go. If there isn't free space, then the DBMS needs to move everything after the insertion point to make room for the new record. That can be very slow, especially in big tables.
    – Joel Brown
    Jan 7 '13 at 15:20
  • 2
    Actually , in newer versions of SQL Server (2005 and up), having a insert hot-spot at the "end" of a table with an ever-increasing clustering key (like an INT IDENTITY) is actually beneficial for performance. Why? Because those relevant pages are most likely already in cache if a next insert occurs. Hotspots were a big problem in earlier versions of SQL Server and their "myth" of being bad is still lingering around - without really being a problem anymore - quite the contrary!
    – marc_s
    Jan 7 '13 at 20:01

This site is temporarily in read only mode and not accepting new answers.

Not the answer you're looking for? Browse other questions tagged .