Please offer your input on this situation: In the application I have worked on for years, the primary key on the CaseInformation table is assigned by the system. Thereafter, however, that number is exposed to users such that they will refer to that case with that number (although they can also search for the case by name, SSN, etc). They cannot modify the case number. To me, this satisfies any logical requirements that the primary key be factless, but some developers on my team contend that we should essentially generate two unique numbers, a primary key and a case number, and never expose the primary key. Any opinions?
Having a hidden surrogate primary key is a good idea (for all the reasons mentioned herein). The case number would then be demoted to a candidate key. On the other hand, there is nothing wrong with keeping the case number as the primary key as long as you don't go around calling it a surrogate! ;-) -- Tod Mckenna
A surrogate (a.k.a. fact-less key Art Bergquist) key is a Key that contains no meaningful data, and isn't updated by, or even displayed to, users. As such, Referential Integrity is easier to maintain.
The antithesis of a surrogate key is a Natural Key.
Surrogate primary keys are unique, system generated, record identifiers. The primary reasons for using surrogate keys are to create small, simple keys and to provide an audit trail. Small keys result in small indices, narrow dependent tables having foreign keys, and joins that are easier to write and faster to execute.
Surrogate keys, by their definition, are meaningless to users and therefore are not likely to be changed by users. In general, it is good practice to never show the surrogate key values to the users. With surrogate keys there is most often at least one Alternate Key in the structure.
What are some real world examples for the uses of a surrogate primary key in a many-to-many link table? One would be a table whose data is only two foreign keys, linking the primary keys of two other tables to form a many-to-many relationship (some call this a Resolver table, others a multi-link[ing] table Art Bergquist).
Let's assume you have an items table with a number of items the company sells. This is table A. This company doesn't have fixed prices for each items, but defines a number of rules that determine the price. Each item might have several of these rules, which are stored in table B. Each rule can also be used with different items to avoid the work of maintainance. Therefore, table C holds the foreign key of both.
You finish the application this way, but as usual, one manager has an additional request. To offer additional value to their customers, they have special offers that are only valid for a certain group of people, in certain states, and so on. This offers are stored in a separate table E. Each price defined for an item can be part of one or more offers, which one finally is used depends on the customer that buys the item, the date and maybe the quantity. Therefore you have a n:m relation between the assignment of the item to the price and the special offers. That's table D which contains the foreign key of table C and the foreign key of table E:
Table A Table B
\ Table E ---- and here it goes on to customer, region, etc.
Don't think it's unrealistic. One of our clients even required a far more complex price calculation scheme.
This has been carried over from a discussion in Join Table: The question is should Item Price Rule (table C) have a surrogate key? The answer depends on how the data can be changed. In this case, the Item Price Rule Specials (table D) will only be affected when an existing Item Price Rule is reassigned to either a new Item (table A) or a new Price Rule (table B). If the Item or the Price Rule can be changed without changing the meaning of the Specials its associated with, then by all means add a surrogate key to Item Price Rule. However, doesn't changing an Item Price Rule call into question the Specials that are associated with it? In the application, wouldn't the user be asked to verify that specials still applied? If so, then it would be just as easy to delete the old Item Price Rule and the associated Item Price Rule Specials and add the verified associations.
Example of many to many relationship using surrogate keys. Assume a Customer table with CustPK as the surrogate PK and a Contacts table with ContPK as the surrogate PK. We need to relate customer to contacts in a situation where a customer may have many contacts and a contact may belong to many customers. This relation should use an intersecting table with at least two fields in it, CustPK and ContPK to provide the link between the customer and contact tables. It is advisable to have a LinkPK surrogate key for the linking table as well.
What is defined herein as a surrogate key may end up being your only key. In many contemporary applications, the need for a user-identifiable code (nee key) is vanishing. For example, if the customer is "Smith, John" then the user should be able to always access data using (at least) "Smith" without the need to consider this customer as "001".
Contributors: Nancy Folsom Alex Feldstein Jim Booth Christof Wollenhaupt Pamela Thalacker, John Koziol jMM Zahid Ali
Category Modeling Category Data Modeling Category Key Fields