Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
2  
If this is for SQL Server, please see: ntext, text, and image data 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 nvarchar(max), varchar(max), and varbinary(max) instead.. And for SQL Server this wouldn't work - you cannot use a TEXT column in an index - an index entry can be 900 bytes at most –  marc_s Jan 7 '13 at 5:49
    
Please remember: Even if you add a surrogate key, you still need to keep a UNIQUE, NOT NULL constraint on the original sets of columns in each table. A surrogate key is just that, a surrogate, and does NOT say anything about the data. You will still need to look up these records using the natural composite keys in order to get the surrogate key and the original constraint needs to remain to keep the data consistent. As a result, you will actually increase insert/update/delete costs by adding the surrogate key, not reduce them. –  Matthew Wood Jan 9 '13 at 17:17
    
@marc_s: That sample code appears to be PostgreSQL code, where TEXT is an alias for a VARCHAR with no upper limit. –  Matthew Wood Jan 9 '13 at 17:20
add comment

3 Answers 3

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

http://stackoverflow.com/questions/3804108/is-email-address-a-bad-primary-key/3804174#3804174

share|improve this answer
    
+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. –  ypercube Jan 7 '13 at 8:40
2  
@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
show 5 more comments

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.

share|improve this answer
    
+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. :) –  Matthew Wood Jan 9 '13 at 17:24
add comment

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.

share|improve this answer
    
+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
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.