Questions tagged [surrogate-keys]
9 questions
18
votes
3 answers
Are surrogate keys a known anti-pattern?
My workplace's database has a pattern that I've not seen before. Every column that is intended to be a key, whether primary or foreign, ends in _SK. This is shorthand for "surrogate key". It appears to be an informal way to tell the developer that…
J. Mini
- 997
- 8
- 20
14
votes
9 answers
Should a surrogate key ever be exposed to a user?
Often in a table that has no natural key, it is still useful for users to be able to have a uniquely generated identifier. If the table has a surrogate primary key (and in such a case you would certainly expect it to) should that key be exposed to…
psr
- 12,846
- 5
- 39
- 67
13
votes
4 answers
Do these specific tables need surrogate keys?
Background
I have this tables
+-------------------------+ +------------------------+
|Airport | |Country |
|-------------------------| |------------------------|
|airport_code string (PK) | |country_code string…
Tulains Córdova
- 39,201
- 12
- 97
- 154
9
votes
2 answers
Is there a canonical source supporting "all-surrogates"?
Background
The "all-PK-must-be-surrogates" approach is not present in Codd's Relational Model or any SQL Standard (ANSI, ISO or other).
Canonical books seems to elude this restrictions too.
Oracle's own data dictionary scheme uses natural keys in…
Tulains Córdova
- 39,201
- 12
- 97
- 154
5
votes
3 answers
How to avoid mixing surrogate key with the rest of the data
Suppose you have a very simple CRUD where you are just storing a simple tuple-like type of data, but its natural primary key is cumbersome for the user to remember (very long, difficult to write, etc.). Further, suppose I want to let users refer to…
Randy Eels
- 51
- 3
3
votes
2 answers
Is it acceptable to use a surrogate key identifier as a URL parameter?
Example:
in the database I have a record such as this:
{
id: 53,
part_number: 'MOTOR-OIL',
description: 'Motor Oil'
}
I wish to use a link that displays this record. I could refer to the record like so, using the surrogate…
Dennis
- 8,157
- 5
- 36
- 68
3
votes
3 answers
Is is OK to use a non-primary key as the id in a rails resource?
I am getting ready to set up a resource for some new api calls to my rails application.
I am planning on calling the resource devices ie
resources :devices
This is going to represent android mobile devices
I know this will get me routes such as
GET…
nPn
- 149
- 1
- 5
0
votes
2 answers
Is it a good idea to let database surrogate key values spill out into other aspects of code (HTML, Controllers, Model, Repository layer)?
Is it acceptable to use id in HTML forms and subsequent code processing (controller, view, model, repository layers)?
For example I need to show something like this on a web page:
Select Motor Choice:
* 460/50/3
* 380/50/3
* 460/60/3
In HTML this…
Dennis
- 8,157
- 5
- 36
- 68
0
votes
1 answer
CRM and external systems: How to associate data with contacts?
This question is about Dynamics CRM but is valid for every system that allows merging entities.
Having data related to a contact in multiple external systems, how would you know which contact the data is related to?
Using ContactId works until that…
Jan Sommer
- 170
- 8