Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
gavinokane
Starting Member
5 Posts |
 Posted - 2009-07-12 : 19:01:33
|
| I have a table of airports which are identified by their IATA code CHAR(3) e.g.DUB = DublinBFS = BelfastetcSo I have a natural unique identifier for the table.Should I use this as a primary key or should I have a numeric primary key for performance reasons (assuming a numeric key is quicker than an text key, happy to be corrected on this)What are the questions I need to ask myself before making this decision?Thanks in advance.RegardsGavin |
|
|
gavinokane
Starting Member
5 Posts |
Posted - 2009-07-12 : 19:21:20
|
| It might be worth adding that this table is going to be fairly static i.e. maybe one or two inserts/deletes annually. So suggestions for this specific example and any comments on the general topic i.e. in the case where the data is not so static. |
 |
|
|
Andrewra
Starting Member
17 Posts |
Posted - 2009-07-13 : 10:53:20
|
| I think it has to do more with your architecture. If you ever plan on doing any dimentionial modeling I would say use a surrogate key using an identity. Check out this site http://www.kimballuniversity.com/html/designtips.html , look at tips #100 and #81Andrew AlexanderLiveLogic llc |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2009-07-13 : 11:30:42
|
| I would avoid using them as a primary key, since there is no guarantee that they will not change in the future(see link below). Make them a unique constraint on the table and then if one changes, you only have to update one row in one table.It is very hard to ensure that the natural key you choose will stay unique. This is one of the most important reasons for using surrogate keys.http://en.wikipedia.org/wiki/International_Air_Transport_Association_airport_code"The assignment of these codes is governed by IATA Resolution 763, and it is administered by IATA headquarters in Montreal. The codes are published biannually in the IATA Airline Coding Directory. The codes are unique at any given point in time, although defunct codes may be re-used after a suitable period of time has elapsed..."CODO ERGO SUM |
|
|
|
gavinokane
Starting Member
5 Posts |
Posted - 2009-07-13 : 11:53:59
|
| Thanks everyone for the feedback. I think i go with the default position of surrogate key.RegardsGavin |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2009-07-13 : 12:45:51
|
| 1) If you use them as a natural key, and they do occasionally change, then the occurrence will be so rare as to be insignificant.2) Create one record for every airport in the world, and you still won't have enough datapoints that it will make one whit of difference in performance whether you go with a natural key or a surrogate key...so 3) Choose whichever makes coding and development simpler. I prefer surrogates for consistency.________________________________________________If it is not practically useful, then it is practically useless.________________________________________________ |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-07-13 : 12:50:49
|
quote:
Originally posted by blindman
1) If you use them as a natural key, and they do occasionally change, then the occurrence will be so rare as to be insignificant.
well, if he has related tables that have a row for every baggage item that ever went in and out of the airport, and other similar tables for flights, passengers, etc. that could be a lot of rows to update. I'm with Michael on this one.Be One with the OptimizerTG |
|
|
|
|
|
|
|
|
Primary Key - Natural versus Surrogate