Questions tagged [natural-key]
Natural Keys consist of a unique value directly related to the purpose of the row(s) related to the key. For instance, a `Clients` table might have a natural key consisting of `EmailAddress`. The opposite of a natural key is a surrogate key. See http://dba.stackexchange.com/tags/surrogate-key/info for more info.
10
questions
2
votes
1
answer
835
views
How to get the "best of both worlds" with natural and surrogate keys? And could DBMSs be better?
I am designing my first database, and I find myself frustrated by the choice between storing an integer or a string for each instance of a categorical variable.
My understanding is that if I have a ...
- 125
2
votes
1
answer
126
views
Good Natural Key For a Physical Mailing Address
I'm trying to figure out what a good natural key for a Physical Mailing (PO Box) address would be. I haven't designed the fields for the table yet, and initially I was going to go with a surrogate key ...
- 123
0
votes
1
answer
45
views
coping with long natural string key
I have inherited a (quite) big database (for my standards at least, since by no means I call myself a DBA or DB dev).
So, on this DB they used a unique natural string as the primary key on EVERY table....
- 131
2
votes
1
answer
520
views
Natural Keys vs Surrogate Keys part 2
A while back, I asked if surrogate keys provide better performance than natural keys in SQL Server. @sqlvogel provided an answer to that question yesterday that caused me to revisit it.
This ...
- 69.7k
5
votes
2
answers
2k
views
Surrogate key vs Natural key
I have a table called devices. Most of the devices that will get stored in this table can be uniquely identified by their serial number and part number. But there are some device types that do not ...
- 368
1
vote
0
answers
71
views
Is the source system typically one of the fields used to uniquely identify business key?
One of the key components of designing a data vault is identifying enterprise-wide unique business keys ("business key" AKA "natural key"). It's not enough to use OrderID to identify records in an ...
- 5,957
4
votes
1
answer
698
views
Normalized Data Store - Confused with prefixes to use
I'm designing a Staging+NDS+DDS Data Warehouse system, where an ETL is going to normalize data from [Staging] and load it into [NDS], which will hold all history.
I've pretty much finished the T-SQL ...
- 894
1
vote
2
answers
544
views
Column suitable for natural key?
I have read many articles now about natural vs surrogate primary keys, and came to the conclusion there is no single best practice.
I have a table that will have around 2000 definite unique values, ...
- 305
28
votes
3
answers
6k
views
Do natural keys provide higher or lower performance in SQL Server than surrogate integer keys?
I'm a fan of surrogate keys. There is a risk my findings are confirmation biased.
Many questions I've seen both here and at http://stackoverflow.com use natural keys instead of surrogate keys ...
- 69.7k
15
votes
4
answers
6k
views
Foreign keys - link using surrogate or natural key?
Is there a best practice for whether a foreign key between tables should link to a natural key or a surrogate key? The only discussion I've really found (unless my google-fu is lacking) is Jack ...
- 492