8
votes
Do natural keys provide higher or lower performance in SQL Server than surrogate integer keys?
A key is a logical feature of a database whereas performance is always determined by physical implementation in storage and by physical operations run against that implementation. It's therefore a ...
- 3,777
6
votes
Accepted
How to get the "best of both worlds" with natural and surrogate keys? And could DBMSs be better?
While ypercube makes a good and logical point for the specific example of Countries, I would otherwise avoid using string-based data types because of the potential unexpected implications that can ...
- 35k
6
votes
Accepted
What are the disadvantages of using surrogate keys?
There are many disadvantages to using surrogate keys, the most important ones IMHO are:
Using natural keys significantly reduces the number of joins that your queries need to perform. With a ...
- 4,078
4
votes
Accepted
What are the consequences, positive or negative, to having a surrogate primary key for a table which already has a guaranteed unique column?
I agree a lot with comments from @MDCCL:
You should analyze each particular case —that is, every particular table within its whole context, that is, the entire database structure— to define if it ...
- 13.3k
4
votes
Accepted
Surrogate vs composite key in hierarchical data structure
With the 1st design, you can enforce that a Part is related (through SubItem and Type) to the same Item. With the second, you can't (through DDL alone, you need triggers as you already mentioned to ...
- 97.3k
4
votes
Surrogate key vs Natural key
Use the surrogate key as the primary key for the moment. When natural keys become available, make them non-nullable unique constraints.
By the YAGNI principle, you should only code for "real-life" ...
- 29.5k
3
votes
Does adding a surrogate key get rid of an identifying relationship between two entities?
For reasons of accuracy, it is indispensable to know the informational characteristics of the business environment under consideration thoroughly so as to supply a proper database design answer but, ...
- 8,470
3
votes
M:M Table Design - Use of PK
Here's the canonical design for a Many-to-Many linking table. You generally want an index to support traversal in each direction, and (at least for SQL Server) it doesn't matter whether you use a ...
- 45.9k
3
votes
Identity versus Composite values for Primary Keys in Many-To-Many Tables
A Many-to-Many mapping table must have a unique index on the foreign key columns. And should normally have an additional unique index on the foreign key columns in the reverse order.
So while adding ...
- 45.9k
3
votes
PostgreSQL surrogate keys: use sequence/bigint or OID?
There are a couple of misunderstandings floating around here.
OIDs were included with every row by default in very old versions of Postgres. The default was soon changed to not include OID columns in ...
- 172k
3
votes
Accepted
Surrogate key resolution, or what am I missing in the Surrogate v Natural argument?
A proper Postgres schema for your example could look like this:
CREATE TABLE selected_task(
selected_task_id serial PRIMARY KEY -- surrogate PK
, user_id int REFERENCES users(user_id)
, ...
- 172k
2
votes
Surrogate key vs Natural key
I think we should start out clarifying that we are talking about Primary Keys, not alternate keys, right?. And since we are talking about Primary Keys, it is best that they do not change over time. ...
- 68.3k
2
votes
Accepted
PostgreSQL surrogate keys: use sequence/bigint or OID?
OIDS
From Docs http://www.postgresql.org/docs/9.4/static/datatype-oid.html
The oid type is currently implemented as an unsigned four-byte
integer. Therefore, it is not large enough to provide ...
1
vote
Accepted
If all associated attributes of an intended primary key are identical, is it still a true primary key?
The problem with this model is that you did not identify all candidate keys in the relation and did not enforce uniqueness of those keys you did not identify. In reality {SSN} and, possibly, {Name, ...
- 25.1k
1
vote
Accepted
Should a inherited table have a new surrogate key, or just use parent table's key?
Strategy #2 has a name. It is called Shared Primary Key. It it widely used with is a type relationships. There is a tag over in Stackoverflow that groups question related to this concept.
See link
- 4,276
1
vote
Natural Keys vs Surrogate Keys part 2
I think your question title, apart from the fact that it is not a question, is a bit misleading. You're not measuring performance of natural/surrogate keys, unlike the question you're referencing. ...
- 25.1k
1
vote
Common term for tenant-based keys
A natural key (AKA business key or domain key) is a key that is used to identify information in the business domain.
A surrogate is a key that is "unseen", internal to the database, meaningless and ...
- 3,777
1
vote
Foreign keys - link using surrogate or natural key?
Older question but still very relevant to data architecture. The debate to go surrogate or go natural is ongoing and of course it depends. Very good points made for both sides here and other questions ...
Only top scored, non community-wiki answers of a minimum length are eligible