Choosing a Primary Key: Natural or Surrogate?

This post overviews strategies for assigning primary keys to a table within a relational database. In particular, it focuses on the issue of when to use natural keys and when to use surrogate keys. Some people will tell you that you should always use natural keys and others will tell you that you should always use surrogate keys. These people invariably prove to be wrong, typically they're doing little more than sharing the prejudices of their "data religion" with you. The reality is that natural and surrogate keys each have their advantages and disadvantages, and that no strategy is perfect for all situations. In other words, you need to know what you're doing if you want to get it right. This article discusses:

Common key terminology  

Strategies for assigning keys

Surrogate key implementation strategies

Tips for effective keys

What to do when you make the "wrong" choice

1. Common Key Terminology

Let's start by describing some common terminology pertaining to keys and then work through an example. These terms are:

• Key. A key is one or more data attributes that uniquely identify an entity.  In a physical database a key would be formed of one or more table columns whose value(s) uniquely identifies a row within a relational table. 
• Composite key. A key that is composed of two or more attributes. 
• Natural key. A key that is formed of attributes that already exist in the real world.  For example, U.S. citizens are issued a Social Security Number (SSN)  that is unique to them (this isn't guaranteed to be true, but it's pretty darn close in practice).  SSN could be used as a natural key, assuming privacy laws allow it, for a Person entity (assuming the scope of your organization is limited to the U.S.). 
• Surrogate key. A key with no business meaning.
• Candidate key. An entity type in a logical data model will have zero or more candidate keys, also referred to simply as unique identifiers (note: some people don't believe in identifying candidate keys in LDMs, so there's no hard and fast rules). For example, if we only interact with American citizens then SSN is one candidate key for the Person entity type and the combination of name and phone number (assuming the combination is unique) is potentially a second candidate key. Both of these keys are called candidate keys because they are candidates to be chosen as the primary key, an alternate key or perhaps not even a key at all within a physical data model. 
• Primary key. The preferred key for an entity type.
• Alternate key. Also known as a secondary key, is another unique identifier of a row within a table. 
• Foreign key. One or more attributes in an entity type that represents a key, either primary or secondary, in another entity type.

Figure 1 presents a physical data model (PDM) for a physical address using the UML notation. In Figure 1 the Customer table has the CustomerNumber column as its primary key and SocialSecurityNumber as an alternate key. This indicates that the preferred way to access customer information is through the value of a person's customer number although your software can get at the same information if it has the person's social security number.  The CustomerHasAddress table has a composite primary key, the combination of CustomerNumber and AddressID.  A foreign key is one or more attributes in an entity type that represents a key, either primary or secondary, in another entity type.  Foreign keys are used to maintain relationships between rows.  For example, the relationships between rows in the CustomerHasAddress table and the Customer table is maintained by the CustomerNumber column within the CustomerHasAddress table. The interesting thing about the CustomerNumber column is the fact that it is part of the primary key for CustomerHasAddress as well as the foreign key to the Customer table. Similarly, theAddressID column is part of the primary key of CustomerHasAddress as well as a foreign key to the Address table to maintain the relationship with rows of Address.

Figure 1. A simple PDM modeling Customer and Address.

  

2. Comparing Natural and Surrogate Key Strategies

There are two strategies for assigning keys to tables:

1. Natural keys.  A natural key is one or more existing data attributes that are unique to the business concept.  For the Customer table there was two candidate keys, in this case CustomerNumber and SocialSecurityNumber. 
2. Surrogate key. Introduce a new column, called a surrogate key, which is a key that has no business meaning. An example of which is the AddressID column of the Address table in Figure 1. Addresses don't have an "easy" natural key because you would need to use all of the columns of the Address table to form a key for itself (you might be able to get away with just the combination of Street and ZipCode depending on your problem domain), therefore introducing a surrogate key is a much better option in this case.  

The advantage of natural keys is that they exist already, you don't need to introduce a new "unnatural" value to your data schema. However, the disadvantage of natural keys is that because they have business meaning they are effectively coupled to your business: you may need to rework your key when your business requirements change. For example, if your users decide to make CustomerNumber alphanumeric instead of numeric then in addition to updating the schema for the Customer table (which is unavoidable) you would have to change every single table whereCustomerNumber is used as a foreign key.

There are several advantages to surrogate keys. First, they aren't coupled to your business and therefore will be easier to maintain (assuming you pick a good implementation strategy). For example, if the Customer table instead used a surrogate key then the change would have been localized to just the Customer table itself (CustomerNumber in this case would just be a non-key column of the table).  Of course, if you needed to make a similar change to your surrogate key strategy, perhaps adding a couple of extra digits to your key values because you've run out of values, then you would have the exact same problem.  Second, a common key strategy across most, or better yet all, tables can reduce the amount of source code that you need to write, reducing the total cost of ownership (TCO) of the systems that you build. The fundamental disadvantage of surrogate keys is that they're often not "human readable", making them difficult for end users to work with. The implication is that you might still need to implement alternate keys for searching, editing, and so on.

The fundamental issue is that keys are a significant source of coupling within a relational schema, and as a result they are difficult to change. The implication is that you generally want to avoid keys with business meaning because business meaning changes. Having said that, I have a tendency to use natural keys for lookup/reference tables, particularly when I suspect that the key values won't change any time soon, as I describe below. Fundamentally, there is no clear answer as to whether or not you should prefer natural keys over surrogate keys, regardless of what the zealots on either side of this religious argument may claim, and that your best strategy is be prepared to apply one strategy or the other whenever it makes sense.

3. Surrogate Key Implementation Strategies

There are several common options for implementing surrogate keys:

1. Key values assigned by the database.  Most of the leading database vendors – companies such as Oracle, Sybase, and Informix – implement a surrogate key strategy called incremental keys. The basic idea is that they maintain a counter within the database server, writing the current value to a hidden system table to maintain consistency, which they use to assign a value to newly created table rows.  Every time a row is created the counter is incremented and that value is assigned as the key value for that row.  The implementation strategies vary from vendor to vendor, sometimes the values assigned are unique across all tables whereas sometimes values are unique only within a single table, but the general concept is the same.
2. MAX() + 1.  A common strategy is to use an integer column, start the value for the first record at 1, then for a new row set the value to the maximum value in this column plus one using the SQL MAX function.  Although this approach is simple it suffers from performance problems with large tables and only guarantees a unique key value within the table.  
3. Universally unique identifiers (UUIDs).  UUIDs are 128-bit values that are created from a hash of the ID of your Ethernet card, or an equivalent software representation, and the current datetime of your computer system.  The algorithm for doing this is defined by the Open Software Foundation (www.opengroup.org).
4. Globally unique identifiers (GUIDs). GUIDs are a Microsoft standard that extend UUIDs, following the same strategy if an Ethernet card exists and if not then they hash a software ID and the current datetime to produce a value that is guaranteed unique to the machine that creates it. 
5. High-low strategy.  The basic idea is that your key value, often called a persistent object identifier (POID) or simply an object identified (OID), is in two logical parts: A unique HIGH value that you obtain from a defined source and an N-digit LOW value that your application assigns itself.  Each time that a HIGH value is obtained the LOW value will be set to zero. For example, if the application that you're running requests a value for HIGH it will be assigned the value 1701. Assuming that N, the number of digits for LOW, is four then all persistent object identifiers that the application assigns to objects will be combination of 17010000,17010001, 17010002, and so on until 17019999.  At this point a new value for HIGH is obtained, LOW is reset to zero, and you continue again. If another application requests a value for HIGH immediately after you it will given the value of 1702, and the OIDs that will be assigned to objects that it creates will be 17020000, 17020001, and so on.   As you can see, as long as HIGH is unique then all POID values will be unique.

The fundamental issue is that keys are a significant source of coupling within a relational schema, and as a result they prove difficult to refactor. The implication is that you want to avoid keys with business meaning because business meaning changes. However, at the same time you need to remember that some data is commonly accessed by unique identifiers, for example customer via their customer number and American employees via their Social Security Number (SSN).  In these cases you may want to use the natural key instead of a surrogate key such as a UUID or POID.    

4. Tips for Effective Keys

How can you be effective at assigning keys?  Consider the following tips:

1. Avoid "smart" keys.  A "smart" key is one that contains one or more subparts which provide meaning.  For example the first two digits of an U.S. zip code indicate the state that the zip code is in. The first problem with smart keys is that have business meaning.  The second problem is that their use often becomes convoluted over time. For example some large states have several codes, California has zip codes beginning with 90 and 91, making queries based on state codes more complex.  Third, they often increase the chance that the strategy will need to be expanded.  Considering that zip codes are nine digits in length (the following four digits are used at the discretion of owners of buildings uniquely identified by zip codes) it's far less likely that you'd run out of nine-digit numbers before running out of two digit codes assigned to individual states.
2. Consider assigning natural keys for simple "look up" tables. A "look up" table is one that is used to relate codes to detailed information. For example, you might have a look up table listing color codes to the names of colors. For example the code 127 represents "Tulip Yellow". Simple look up tables typically consist of a code column and a description/name column whereas complex look up tables consist of a code column and several informational columns. 
3. Natural keys don't always work for "look up" tables. Another example of a look up table is one that contains a row for each state, province, or territory in North America.  For example there would be a row for California, a US state, and for Ontario, a Canadian province. The primary goal of this table is to provide an official list of these geographical entities, a list that is reasonably static over time (the last change to it would have been in the late 1990s when the Northwest Territories, a territory of Canada, was split into Nunavut and Northwest Territories). A valid natural key for this table would be the state code, a unique two character code – e.g. CA for California and ON for Ontario.  Unfortunately this approach doesn't work because Canadian government decided to keep the same state code, NW, for the two territories.
4. Your applications must still support "natural key searches". If you choose to take a surrogate key approach to your database design you mustn't forget that your applications must still support searches on the domain columns that still uniquely identify rows.  For example, your Customer table may have a Customer_POID column used as a surrogate key as well as a Customer_Number column and a Social_Security_Number column.  You would likely need to support searches based on both the customer number and the social security number.  Searching is discussed in detail in Best Practices for Retrieving Objects from a Relational Database.
5. Don't naturalize surrogate keys. As soon as you display the value of a surrogate key to your end users, or worse yet allow them to work with the value (perhaps to search), you have effectively given the key business meaning. This in effect naturalizes the key and thereby negates some of the advantages of surrogate keys.

5. What to Do When You Make the "Wrong" Choice

First of all, don't worry about this: You're only human, and no matter how good you are at database design you're going to make mistakes. The good news is that as I show in The Process of Database Refactoring it is possible, albeit it may require a bit of work, to replace a natural key with a surrogate key (or vice versa). To replace a natural key with a surrogate you would apply the Introduce Surrogate Key refactoring, as you see depicted in Figure 2 to replace the key of the Order table. To replace a surrogate key with a natural key you would apply the Replace Surrogate Key with Natural Key refactoring, as you see in Figure 3 to replace the key of the State table.

Figure 2. Replacing the natural key of the Order table.

Figure 3. Replacing the surrogate key within the State table.

http://www.agiledata.org/essays/keys.html
http://www.techrepublic.com/blog/10-things/10-tips-for-choosing-between-a-surrogate-and-natural-primary-key/
http://sqlmag.com/business-intelligence/surrogate-key-vs-natural-key

Key Differences Between DBMS and RDBMS

A database is a collection of data that is stored in an organized manner. This data can either be on a computer or on paper. It is, of course, more efficient to store data on a computer, as the computerization of this data makes it easy to retrieve and perform operations on. Today, in the information age, databases of some kind are maintained by all organization, big and small. They are essential in ensuring that the day to day operations of an organization can run smoothly.

An Introduction to Database Systems

There are several software-based products that help you build and maintain databases. These software products are known as database management systems (DBMS). In addition to letting you build a database, most DBMS applications will interact with a user, or multiple users. They will also let you perform operations on the data in their repository. DBMS systems are always based on a computer language (which can vary). A user has to type commands in this language if he wants to interact with the data on the system – as well as the software he is using.

Key Difference between DBMS and RDBMS

So what is the main difference between DBMS and RDBMS? The key difference is that RDBMS (relational database management system) applications store data in a tabular form, while DBMS applications store data as files. Does that mean there are no tables in a DBMS? There can be, but there will be no “relation” between the tables, like in a RDBMS. In DBMS, data is generally stored in either a hierarchical form or a navigational form. This means that a single data unit will have one parent node and zero, one or more children nodes. It may even be stored in a graph form, which can be seen in the network model.

In a RDBMS, the tables will have an identifier called primary key. Data values will be stored in the form of tables. The relationships between these data values will be stored in the form of a table as well.  Every value stored in the relational database is accessible. This value can be updated by the system. The data in this system is also physically and logically independent.

You can say that a RDBMS is an in an extension of a DBMS, even if there are many differences between the two. Most software products in the market today are both DBMS and RDBMS compliant. Essentially, they can maintain databases in a (relational) tabular form as well as a file form, or both. This means that today a RDBMS application is a DBMS application, and vice versa. However, there are still major differences between a relational database system for storing data and a plain database system.

History of DBMS and RDBMS

Database management systems first appeared on the scene in 1960 as computers began to grow in power and speed. In the middle of 1960, there were several commercial applications in the market that were capable of producing “navigational” databases. These navigational databases maintained records that could only be processed sequentially, which required a lot of computer resources and time.

Relational database management systems were first suggested by Edgar Codd in the 1970s. Because navigational databases could not be “searched”, Edgar Codd suggested another model that could be followed to construct a database. This was the relational model that allowed users to “search” it for data. It included the integration of the navigational model, along with a tabular and hierarchical model.

The Client Server Architecture

Database management systems like the ones you’ll learn about in this course (at least the pure DBMS applications) do not support the client-server architecture, while relational database management systems do. What is the client-server database model exactly? In a client-server database model, data is stored in a central location on a server. This server can share the data between one or more users, which are referred to as clients. However, this is not a distinction that is relevant today, where a DBMS program is a RDBMS program, and vice versa.

Ease of Access

It is generally easier to access data that is stored in a relational database. This is because the data in a relational database follows a mathematical model for categorization. Also, once we open a relational database, each and every element of that database becomes accessible, which is not always the case with a normal database (the data elements may need to be accessed individually).  

It is also easier to find data in a relational database. You can “query” a relational database in its native language without knowing the value of a key or index.

Storage Standards

Relational databases are harder to construct, but they are better structured and more secure. They follow the ACID (atomicity, consistency, isolation and durability) model when storing data. The relational database system will also impose certain regulations and conditions that may not allow you to manipulate data in a way that destabilizes the integrity of the system.

In a regular database, the data may not be stored following the ACID model. This may introduce inconsistencies in the database. It may even cause the database to become unstable over time or it may put the security of the data at risk.

Database Normalization Concepts

Description

Database normalization is the process of organizing the attributes and tables of a relational database to minimize data redundancy.

Normalization involves re-factoring a table into smaller (and less redundant) tables but without losing information; defining foreign keys in the old table referencing the primary keys of the new ones. The objective is to isolate data so that additions, deletions, and modifications of an attribute can be made in just one table and then propagated through the rest of the database using the defined foreign keys.

Normalization can be described as:

• It is a technique for designing relational database tables to minimize duplication of information.
• Normalization is a practice to safeguard the database against logical and structural anomalies.
• Normalization is also termed as Canonical synthesis by the experts.
• It is used to keep data consistent and check that no loss of data as well as integrity is there.
• Its complexity may lead to higher degree of join operations which sometimes lead to the degraded throughput times.

Normalization Objective

1. To free the collection of relations from undesirable insertion, update and deletion dependencies;
2. To reduce the need for restructuring the collection of relations, as new types of data are introduced, and thus increase the life span of application programs;
3. To make the relational model more informative to users;
4. To make the collection of relations neutral to the query statistics, where these statistics are liable to change as time goes by.

Database Modification Anomalies

When an attempt is made to modify (update, insert into, or delete from) a table, undesired side-effects may follow. Not all tables can suffer from these side-effects; rather, the side-effects can only arise in tables that have not been sufficiently normalized. An insufficiently normalized table might have one or more of the following characteristics:

Update Anomaly:

The same information can be expressed on multiple rows; therefore updates to the table may result in logical inconsistencies. For example, each record in an "Employees' Skills" table might contain an Employee ID, Employee Address, and Skill; thus a change of address for a particular employee will potentially need to be applied to multiple records (one for each skill). If the update is not carried through successfully—if, that is, the employee's address is updated on some records but not others—then the table is left in an inconsistent state. Specifically, the table provides conflicting answers to the question of what this particular employee's address is. This phenomenon is known as an update anomaly.

An update anomaly. Employee 519 is shown as having different addresses on different records.

Insertion Anomaly:

There are circumstances in which certain facts cannot be recorded at all. For example, each record in a "Faculty and Their Courses" table might contain a Faculty ID, Faculty Name, Faculty Hire Date, and Course Code—thus we can record the details of any faculty member who teaches at least one course, but we cannot record the details of a newly hired faculty member who has not yet been assigned to teach any courses except by setting the Course Code to null. This phenomenon is known as an insertion anomaly.

 An insertion anomaly. Until the new faculty member, Dr. Newsome, is assigned to teach at least one course, his details cannot be recorded.

Deletion Anomaly:

Under certain circumstances, deletion of data representing certain facts necessitates deletion of data representing completely different facts. The "Faculty and Their Courses" table described in the previous example suffers from this type of anomaly, for if a faculty member temporarily ceases to be assigned to any courses, we must delete the last of the records on which that faculty member appears, effectively also deleting the faculty member, unless we set the Course Code to null in the record itself. This phenomenon is known as a deletion anomaly.

A deletion anomaly. All information about Dr. Giddens is lost if he temporarily ceases to be assigned to any courses.

First Normal Form (1NF)

As per First Normal Form, no two Rows of data must contain repeating group of information i.e each set of column must have a unique value, such that multiple columns cannot be used to fetch the same row. Each table should be organized into rows, and each row should have a primary key that distinguishes it as unique.

The Primary key is usually a single column, but sometimes more than one column can be combined to create a single primary key. For example consider a table which is not in First normal form

Student Table :

Student	Age	Subject
Adam	15	Biology, Maths
Alex	14	Maths
Stuart	17	Maths

In First Normal Form, any row must not have a column in which more than one value is saved, like separated with commas. Rather than that, we must separate such data into multiple rows.

Student Table following 1NF will be:

Student	Age	Subject
Adam	15	Biology
Adam	15	Maths
Alex	14	Maths
Stuart	17	Maths

Using the First Normal Form, data redundancy increases, as there will be many columns with same data in multiple rows but each row as a whole will be unique.  

Second Normal Form (2NF)

As per the Second Normal Form there must not be any partial dependency of any column on primary key. It means that for a table that has concatenated primary key, each column in the table that is not part of the primary key must depend upon the entire concatenated key for its existence. If any column depends only on one part of the concatenated key, then the table fails Second normal form.

In example of First Normal Form there are two rows for Adam, to include multiple subjects that he has opted for. While this is searchable, and follows First normal form, it is an inefficient use of space. Also in the above Table in First Normal Form, while the candidate key is {Student, Subject}, Age of Student only depends on Student column, which is incorrect as per Second Normal Form. To achieve second normal form, it would be helpful to split out the subjects into an independent table, and match them up using the student names as foreign keys

New Student Table following 2NF will be :

Student	Age
Adam	15
Alex	14
Stuart	17

In Student Table the candidate key will be Student column, because all other column i.e Age is dependent on it.

New Subject Table introduced for 2NF will be :

Student	Subject
Adam	Biology
Adam	Maths
Alex	Maths
Stuart	Maths

In Subject Table the candidate key will be {Student, Subject} column. Now, both the above tables qualifies for Second Normal Form and will never suffer from Update Anomalies. Although there are a few complex cases in which table in Second Normal Form suffers Update Anomalies, and to handle those scenarios Third Normal Form is there.

Third Normal Form (3NF)

Third Normal form applies that every non-prime attribute of table must be dependent on primary key. The transitive functional dependency should be removed from the table. The table must be in Second Normal form. For example, consider a table with following fields.

Student_Detail Table:

Student_id	Student_name	DOB	Street	city	State	Zip

In this table Student_id is Primary key, but street, city and state depends upon Zip. The dependency between zip and other fields is called transitive dependency. Hence to apply 3NF, we need to move the street, city and state to new table, with Zip as primary key.

New Student_Detail Table :

Student_id	Student_name	DOB	Zip

Address Table:

Zip	Street	city	state

The advantage of removing transtive dependency is,

• Amount of data duplication is reduced.
• Data integrity achieved. 

Boyce and Codd Normal Form (BCNF)

 Boyce and Codd Normal Form is a higher version of the Third Normal form. This form deals with certain type of anamoly that is not handled by 3NF. A 3NF table which does not have multiple overlapping candidate keys is said to be in BCNF.

Normalizing an Example Table

These steps demonstrate the process of normalizing a fictitious student table.

1. Unnormalized table: 
   
   
   Student#	Advisor	Adv-Room	Class1	Class2	Class3
   1022	Jones	412	101-07	143-01	159-02
   4123	Smith	216	201-01	211-02	214-01

2. First Normal Form: No Repeating Groups
   
   
   Tables should have only two dimensions. Since one student has several classes, these classes should be listed in a separate table. Fields Class1, Class2, and Class3 in the above records are indications of design trouble. 
   
   
   Spreadsheets often use the third dimension, but tables should not. Another way to look at this problem is with a one-to-many relationship, do not put the one side and the many side in the same table. Instead, create another table in first normal form by eliminating the repeating group (Class#), as shown below:
   
   
   Student#	Advisor	Adv-Room	Class#
   1022	Jones	412	101-07
   1022	Jones	412	143-01
   1022	Jones	412	159-02
   4123	Smith	216	201-01
   4123	Smith	216	211-02
   4123	Smith	216	214-01

3. Second Normal Form: Eliminate Redundant Data
   
   
   Note the multiple Class# values for each Student# value in the above table. Class# is not functionally dependent on Student# (primary key), so this relationship is not in second normal form.
   
   
   The following two tables demonstrate second normal form: 
   
   
   Students:
   
   
   Student#	Advisor	Adv-Room
   1022	Jones	412
   4123	Smith	216

   
   
   Registration:
   
   
   Student#	Class#
   1022	101-07
   1022	143-01
   1022	159-02
   4123	201-01
   4123	211-02
   4123	214-01

4. Third Normal Form: Eliminate Data Not Dependent On Key
   
   In the last example, Adv-Room (the advisor's office number) is functionally dependent on the Advisor attribute. The solution is to move that attribute from the Students table to the Faculty table, as shown below:
   
   Students:
   
   
   Student#	Advisor
   1022	Jones
   4123	Smith

   
   
   Faculty:
   
   
   Name	Room	Dept
   Jones	412	42
   Smith	216	42