What is a surrogate key ?
jwc, December 03, 2002 - 1:28 pm UTC
I have heard this term in a lot of places.
what is a surrogate key
December 03, 2002 - 1:40 pm UTC
Main Entry: 1sur·ro·gate
Pronunciation: 's&r-&-"gAt, 's&-r&-
Function: transitive verb
Inflected Form(s): -gat·ed; -gat·ing
Etymology: Latin surrogatus, past participle of surrogare to choose in place of another, substitute, from sub- + rogare to ask -- more at RIGHT
Date: 1533
: to put in the place of another: a : to appoint as successor, deputy, or substitute for oneself b : SUBSTITUTE
it is a substitute primary key -- for when you don't have a real one, as above.
Don't joke
Martin, December 04, 2002 - 3:32 am UTC
"This would be like you changing your DNA halfway through your life."
The way technology is going... we're not far off making this possible! ;-)
Sagi, December 04, 2002 - 10:09 am UTC
Hi Tom,
You said that Surrogate Key is a substitute for Primary key.
Do you mean to say that we have a column that is populated with SEQUENCE automatically, which is called Surrogate Key (Technical Term)?
I would appreciate if you could through some more detail on this....please.
Thanx in advance.
Regards,
Sagi
December 04, 2002 - 12:38 pm UTC
an ID column populated via a sequence is known as a surrogate key.
if the table has a true primary key -- it is known as a "natural key"
Performance of surrogate vs natural
Reader, January 24, 2003 - 9:24 am UTC
We have some consultants here now who are saying that "good warehouse design practice is to use a 4-byte surrogate value instead of a business key value for keys." They are saying that query performance is compromised when joins are performed on the longer business key fields, and that overall performance is affected by the resulting longer fact tables since more database pages must be read to process a result set. Your comments, please.
January 24, 2003 - 11:03 am UTC
ask them what a 4 byte surrogate value is in Oracle please.
I'd be REALLY interested to here their response. Then, when they say "integer", we can have a laugh as we show them the door....
Re: Performance of surrogate vs natural
Reader, January 24, 2003 - 11:23 am UTC
Yes, I had a laugh at that, too. But are the other points valid, presuming the use of a sequence as the surrogate?
January 24, 2003 - 12:49 pm UTC
not really but a little.
An index on N bytes will be smaller then an index on (N+M) bytes of data.
So, if you have a compound key that is on average 25 bytes and can replace it with a number (which varies in length from 0..22 bytes and whose avg would be smaller then 25 bytes by alot), then
o you cache more index blocks
o you compare smaller strings
It is a very common practice to use surrogate keys, yes. Sort of funny they would want to go with a 4 byte integer (in a database that has no such thing) since that would limit them to a set of 4billion rows which in a data warehouse is pushing it. Especially with incremental loads and stuff. Do you really want to be limited to
a) 4 billion
b) having to play "keeper of the keys" cause you cannot just use an increasing number -- you'll have to reuse values with such a small namespace.
Surrogate keys...
Kashif, July 07, 2003 - 4:57 pm UTC
Hi Tom,
Since performance becomes the de facto standard in many cases for the success of a warehouse, why would someone NOT use surrogate keys? Secondly, are surrogate keys the same as "factless" keys?
Thanks.
Kashif
July 07, 2003 - 5:11 pm UTC
generation of surrogate keys and mapping them back and forth during a load will not make it faster to load.
generation of surrogate keys won't necessarily make queries any faster either -- if the natural key is not "huge"
I'd have to see the text book definition of "factless" key.
SKs: Is not only about performance
Juanje, July 08, 2003 - 5:01 am UTC
Hi,
well, the use of SKs in Datawarehouse environments is not only simplicity of star design or performance. You often need to populate your dimensions with extra records not found in the source table, such as "UNKNOWN" or "NOT APPLIES" records. If you don't use SKs, What PK value would you use for this extra records? How can you be sure that the values you choose won't collide with future records from the source table? Also, taking your dimensional schemas away from possible source key changes is a good idea. Of course creating and maintaining SKs adds complexity and is extra work for ETL processes.
July 08, 2003 - 7:39 am UTC
I think we are in agreement
One Individual Sequence for each Surrogate Key ?
Arash, June 08, 2004 - 10:46 am UTC
Hi Tom,
I strongly believe in using surrogate key as PK in OLTP application. I have a question regarding the best way we can generate this auto number key? In my last project, we didn't use sequence because we didn't want have a gap! So we had Before Insert trigger which select max(id)+1 from Table.
Now, for my new project, gap is not important and I thought it's better that I use sequence.
1- Is it better to have an individual sequence for each table? or
- Just a sequence for all tables or
- One sequence for all small tables and an individual sequence for each big table.
2- Do you agree with having just one user as the owner of all objects?
Please let me know your valuable idea.
As always, thank you for your help.
-Arash
June 08, 2004 - 11:58 am UTC
gap free sequences = one person at a time does a transaction in your system.
that is the only way to do it. ONE PERSON AT A TIME.
ONE TRANSACTION.
no concurrency.
It is neither scalable, nor have I seen an actual case where it is necessary.
It is infinitely better to use a sequence. (i hope you had the proper manual locking in place, else your system is either having duplicate keys or raising ora-1 lots of times)
1) I would have a sequence per object.
2) depends. are all of the objects logically belonging together?
Thank you so much
A reader, June 08, 2004 - 12:06 pm UTC
gap free sequences
Matthias Rogel, June 08, 2004 - 12:06 pm UTC
alter sequence my_surr_key_seq nocache;
June 08, 2004 - 1:21 pm UTC
can you spell "rollback"
or "system crash"
now what? welcome to "gap city"
;-)
Matthias Rogel, June 09, 2004 - 3:13 am UTC
Designing a table load with a surrogate key
Jon, August 30, 2005 - 6:43 pm UTC
Tom,
Would you please advise me on the use of surrogate keys in the following situation.
I am designing an MIS data warehouse where a fact table is holding data aggregated by month. A dimension table is holding information that must be historically accurate.
For example:
If Company 'XYZ' has a sales rep named 'BOB' in Jan/2005
Then the sales rep for 'XYZ' changed to 'MARK' in Feb/2005 onward
They need to be able to produce a report that shows:
RPT_DATE SALES REP AMOUNT SOLD
JAN/2005 BOB 100,000
FEB/2005 MARK 200,000
MAR/2005 MARK 200,000
...
...
The design approach that I proposed for this is to use an ordered sequence as the surrogate key on the dimension table that would change whenever the attributes changed.
Upon loading the fact table the ETL job would have to select max(dim_id) for their natural key from the dimension table.
Upon loading the dimension table the ETL job would get a new sequence whenever an attribute set does not exist in the dimension table.
I have been browsing through a few DW books and have not come up anything that either validate this approach or provides a better solution. Would you please tell me if this is the best design for this situation.
Regards,
Jon
August 31, 2005 - 12:50 pm UTC
Designing a table load with a surrogate key followup
Jon, August 31, 2005 - 12:02 pm UTC
Tom,
Here is the DDL for my question above.
Thanks,
Jon
>CREATE TABLE org_se_dim (
dim_org_ae_id number(7) NOT NULL,
org_id number(7) NOT NULL,
ae_account_id number(7) NOT NULL,
ae_first_name varchar(20) NOT NULL,
ae_last_name varchar(30) NOT NULL)
CREATE UNIQUE INDEX pk1_org_se_dim ON org_se_dim (dim_org_ae_id)
LOGGING
NOPARALLEL;
ALTER TABLE org_se_dim ADD (
CONSTRAINT pk1_org_se_dim PRIMARY KEY (dim_org_ae_id));
CREATE SEQUENCE seq_dim_org_ae_id INCREMENT BY 1 START WITH 1 MINVALUE 1 NOCYCLE NOCACHE ORDER
CREATE TABLE org_sales (
org_id number(7) NOT NULL,
dim_org_ae_id number(7) NOT NULL,
rpt_date date NOT NULL,
sales_amt number(10,2) NOT NULL)
CREATE UNIQUE INDEX pk1_org_sales ON org_sales
(org_id, dim_org_ae_id, rpt_date)
LOGGING
NOPARALLEL;
ALTER TABLE org_sales ADD (
CONSTRAINT pk1_org_sales PRIMARY KEY (org_id, dim_org_ae_id, rpt_date));
ALTER TABLE org_sales ADD (
CONSTRAINT ref1_org_sales FOREIGN KEY (dim_org_ae_id)
REFERENCES org_se_dim (dim_org_ae_id));
Sequences usages
Thirumaran, November 25, 2005 - 12:16 am UTC
HiTom,
I have certain queries for your response regarding sequences. Below #1 are your answers (a),(b) are my question for these points
#1)generation of surrogate keys won't necessarily make queries any faster either -- if the natural key is not "huge"
a) can you please eloborate #1
b) if the natural key is not "huge" ? what is the limitations ?
#2) I'd have to see the text book definition of "factless" key.
a) w.r.t #2 i found a website using the same definition "factless key"
</code>
http://www.dfpug.de/konf/konf_1998/07_c_s/e_back/e_back.htm <code>
Below Figure 2. The Sample data
"Our factless primary key here"
what is a factless key ?
Thanks in ADV
Thirumaran
November 25, 2005 - 10:05 am UTC
#1) means that
where t1.surrogate_key = t2.surrogate_key
isn't necessarily faster than
where t1.natural_key = t2.natural_key
we are just comparing bytes.
there are no "limitations", just math. If you compare a couple of bytes - it doesn't matter if the bytes represented a date, a number or a string.
#2)
scary paper there isn't it. seems to be saying "do all of this work and you'll be database independent".
like by generating your own sequences from a table and so on....
but, they didn't have the definition there either - I was asking myself for the textbook definition.
My tsql is a little rusty, but isn't anyone else afraid of the following routine for generating sequences:
CREATE PROCEDURE sp_getnewid
@tcEntity char(12)
AS
begin
/* create a simple cursor containing the new id: */
select ID from uniqueid
where uniqueid.ENTITY = @tcEntity
/* increment the field for the next time: */
update uniqueid
set ID = (ID+1)
where ENTITY = @tcEntity
/* return success: */
return ( 1 )
end
ouch, works well - for a SINGLE USER. Hate to see what happens with more than one user..... Hmmm. interesting concurrency control problems there. Wondering what happens when the duplicates start being generated....
Surrogate keys
AD, December 12, 2005 - 8:55 pm UTC
Tom,
Your advise on the following is very much appreciated.
I'll be creating a set of extract files from Oracle database that will be fed to Mainframe. As part of this requirement, every extract file should have a header, body and a trailer. I am thinking of creating separate header /trailer tables for each of these extract files, so that when I create the file I can query these tables along with the tables that are the data source for the body. Having separate tables for header/trailer is a good idea or just a waste? If not, since these tables will have only one record each, do we need to create any surrogate keys, if there are no obvious natural keys available?
Many thanks for your time.
Regards
December 13, 2005 - 8:29 am UTC
what are to be the contents of these "header" and "trailer" thingys?
contents of header/trailer
AD, December 13, 2005 - 1:05 pm UTC
Tom,
Thanks for your reply.
The header will have info like record_type, file_id, file sequence_no, file creation time etc. Whereas trailer will have record_type, file_id, record counts. Header and trailer each will be a single record.
Regards
December 13, 2005 - 1:27 pm UTC
I'd probably just put that into extraction program itself (not knowing what a file_id, file_sequence_no is of course...)
composite PK everywhere
A reader, June 19, 2006 - 12:34 pm UTC
Hi Tom
We have a new coming reporting application (still in design state) which are full of composite primary keys, from one column to 5 columns.
The data loading strategy is as follows:
- Parent table refreshed from 3 sources using Read Only Materialized Views
- To load child table, unload the data from 3 sources to flat file and load to the destination using SQL*Loader
We are facing a problem here, to avoid locking issues when refreshing parent tables we index the foreign keys columns as well, for most of child tables we ending up having indexes like this
primary key
------------
col1, col2, col3, col4, col5
FK index to parent A
------------
col1, col2, col4
FK index to parent B
------------
col1, col2, col5
We have columns which ends up indexed as many as 5 times! Do you think we should use surrogate keys in this situation?
Sheilla
June 19, 2006 - 6:45 pm UTC
how would surrogate keys cut down on the aggregate number of indexes.
If you have natural keys of a reasonable size, I see no problem using them.
composite PK everywhere
A reader, June 20, 2006 - 3:17 am UTC
Hi
The problem is we have columns which are indexed at least 2 times and at most 5 times (average 3 times). Also there are tables with 10 columns composite Primary Key!
June 20, 2006 - 9:45 am UTC
why is that a problem, as compared to have N indexes.
You would either have:
a) N indexes
b) N indexes
N would not change right? so as long as the natural keys are reasonable, there is nothing major league wrong with using them.
the problem is.......
A reader, June 20, 2006 - 11:03 am UTC
T1 is parent_1 -> PK make up of c1, c2, c3
T2 is parent_1 -> PK make up of c1, c2, c4
T3 is parent_1 -> PK make up of c1, c2, c5
T2 is child of T1, T2, and T3 so to avoid locking we indexed FKs
T2 -> c1, c2, c3, c4, c5, c6
index for FK to T1 -> c1, c2, c3
index for FK to T2 -> c1, c2, c4
index for FK to T3 -> c1, c2, c5
That is the problem I was saying. Or not really a problem as you say! (When the CBO can choose the proper index)
June 21, 2006 - 9:03 am UTC
The CBO would choose the index, yes.
You have either
a) N indexes or....
b) N indexes
You have N indexes in both cases. As I've said, as long as your natural keys are reasonable in size, there is nothing wrong in using them.
the problem is.......
A reader, June 20, 2006 - 11:06 am UTC
Make a pardon, some data were wrong, here is the correct
T1 is parent_1 -> PK make up of c1, c2, c3
T2 is parent_2 -> PK make up of c1, c2, c4
T3 is parent_3 -> PK make up of c1, c2, c5
T4 is child of T1, T2, and T3 so to avoid locking we indexed FKs
T4 -> c1, c2, c3, c4, c5, c6
index for FK to T1 -> c1, c2, c3
index for FK to T2 -> c1, c2, c4
index for FK to T3 -> c1, c2, c5
That is the problem I was saying. Or not really a problem as you say! (When the CBO can choose the proper index)
Sorry for spamming
Good
Vinod, June 20, 2006 - 1:49 pm UTC
"This would be like you changing your DNA halfway through your life."
There couldn't have been a better line to explain the concept.. Great!!!
Intelligent surrogate key
Yogesh, October 26, 2006 - 3:30 am UTC
Hi Tom,
We are somewhat facing similar situation you have described in the beginning. For our date dimension shall we go for intelligent surrogate key or dumb surrogate key , thats what I am trying to figure out.
I did a test run on sample tables one with intelligent surrogate key where date key is 20060101,20060102,20060103 etc. Another table with dumb surrogate key for date key as 1,2,3 etc.
For all the operations like joins , sorts , indexing the cost of queries with date key 20060101 etc was higer(not much). Also the bytes processed were also on higher side. Considering that the fact tables typically have millions of records, isnt it better from performance point to have dumb surrogate keys ?
Both the tables in the following test have identical number of rows and other physical structures and also both are analyzed.
/********** Sample output start *********/
SQL> select * from date_s;
COL1 COL2
---------- ----------
1 2
2 3
3 4
4 5
5 6
6 7
7 8
8 9
9 10
10 11
11 12
....
SQL> select * from date_i;
COL1 COL2
---------- ----------
20061027 1
20061028 2
20061029 3
20061030 4
20061031 5
20061101 6
20061102 7
20061103 8
20061104 9
20061105 10
20061106 11
.....
SQL> select count(*) from date_s;
COUNT(*)
----------
498232
SQL> select count(*) from date_i;
COUNT(*)
----------
498232
SQL> set autotrace traCEONLY EXPLAIN;
SQL> select * from date_s order by col1;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2052 Card=498232 B
ytes=3985856)
1 0 SORT (ORDER BY) (Cost=2052 Card=498232 Bytes=3985856)
2 1 TABLE ACCESS (FULL) OF 'DATE_S' (TABLE) (Cost=235 Card=4
98232 Bytes=3985856)
SQL> select * from date_i order by col1 ;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2163 Card=498232 B
ytes=4484088)
1 0 SORT (ORDER BY) (Cost=2163 Card=498232 Bytes=4484088)
2 1 TABLE ACCESS (FULL) OF 'DATE_I' (TABLE) (Cost=253 Card=4
98232 Bytes=4484088)
/********* End of sample output*********/
I deeply appreciate your help and just love your site.
Thanks
Yogesh
October 26, 2006 - 11:54 am UTC
think about it - with the "intelligent" (i am not at all fond of that naming convention, it conveys an opinion there in the name itself - why are they "intelligent". They are NATURAL KEYS) will allow you to skip a join in many cases! WINNER.
I do not get the relevance of your test at all. So what, you compared a table with short numbers versus a table with wide numbers. Are you saying that in REAL LIFE, you would not be retrieving the date in both cases anyway (your test case doesn't seem to be showing us anything of relevance regarding the real work you would do in real life)
surrogate Key
Yogesh, October 27, 2006 - 1:57 am UTC
Always great to get your reply.
The two tables I have used DATE_S and DATE_I are two varities of date dimension table in our model we are debating. DATE_S is having surrogate keys (column COL1) generated using dumb sequence. DATE_I is having surrogate keys (column COL1) having built in intelligence. When I am sorting on the surrogate keys in both cases, the cost of query is higher for DATE_I. Similar trend I had observed for similar queries on these tables with joins, filters etc.
Besides that while using DATE_S, I can use the surrogate key and corresponding fact table column as number(4) datatype. Whereas for DATE_I table, the key should be number(8) for both dimension and fact. As this key will be used in all the fact tables across millions of records, the size might be issue. Am not sure. Even the network traffic will be (slightly) higher in case of DATE_I type model. Isn't that correct?
I am not clear how it will reduce the joins. We will be joining the fact and dimension tables in both cases right?
October 27, 2006 - 7:44 am UTC
who cares about the cost
how is the actual observed response time.
if date_i is a DATE, has the DATE, why do you need to join to the dimension?????
PK values from sequence - security concern?
Aaron, October 27, 2006 - 1:11 pm UTC
Hi Tom -
I just got out of a meeting with a consultant who was trying to talk us out of using an Oracle SEQUENCE to populate a surrogate key - and his argument was that it is a security concern. I was so stunned to hear this that I couldn't come up with a response to disprove it. Have you ever heard of anything like this before, or can you think of any way it might be a security problem? (or could it just be that the guy was drunk?)
Anyway, he suggested it was better to use something like :
create table t (
content_ID RAW(16) default sys_guid(),
name VARCHAR2(10));
insert into t values ('system');
commit;
instead of something like:
create table t (
content_ID NUMBER
name VARCHAR2(10));
which would get populated by a sequence like:
CREATE SEQUENCE cID_sequence INCREMENT BY 1 START WITH 1 NOMAXVALUE NOCYCLE CACHE 50 ORDER);
using something like:
INSERT INTO table VALUES (cID_sequence.nextval, 'system');
commit;
We don't care about gaps, we just care about having a unique ID for each value of the content_ID.
Can you let me know which is your preference (RAW with sys_guid() or SEQUENCE) and why, and any comment on the security notion mentioned earlier?
Thanks Tom
October 27, 2006 - 8:08 pm UTC
depends on where the key is used.
Look at my url's - I pass a displayid around. It is basically a function of a random number, some other stuff and a sequence. It makes them "unguessable".
If you are generating "sessionids" - it might be nice to make them NON-GUESSABLE .
If you are generating encryption keys - the same.
If you are assigning normal keys to most data, you need not worry about.
So - they had a point, but the point only needs to be made in very very special cases.
sys_guid as primary key
Aaron, October 29, 2006 - 2:11 pm UTC
Thanks Tom.
Is there any way to ensure that sys_guid() will be unique? I don't suppose there is a way, because it is a randomly generated set of numbers; even though the chances are very, very slim that a duplicate would be generated . . there is still a chance. So using sys_guid() as a primary key might not be the best idea (better to use a sequence for PK field). Or would it be worth the risk in order to gain the "unguessable"-ness of what sys_guid can provide?
Thanks in advance for your input, I appreciate it.
October 29, 2006 - 3:10 pm UTC
unique constraints would make sure it was unique... The probability of a clash is so sufficiently small as to be ignorable as long as you have constraints in place.
sweet
A reader, October 29, 2006 - 3:13 pm UTC
Excellent. Thanks for your input, I appreciate it :-)
Philosophical Question
Kurt, October 07, 2008 - 5:36 pm UTC
In your response to the original question, you say:
[Quote]
If the primary key has a chance -- however remote -- of changing, well, then it is NOT
the primary key.
This would be like you changing your DNA halfway through your life.
[End Quote]
That seems like a statement any sane DBA or application developer would agree with.
So, if the columns of a Primary Key are inherently (logically) unchanging, then why does Oracle allow updates to Primary Key columns? Conformance to some SQL specification? Backward compatibility?
I would think Oracle could shortcut a lot of processing by simply disallowing the modification of Primary Key columns.
(Please forgive the "philosophical" nature of the question.)
Thanks.
October 08, 2008 - 9:59 pm UTC
... Conformance to some SQL
specification? ...
there are in fact no rules against it unfortunately.
Database Design Resources
Arvind Mishra, January 14, 2009 - 4:53 pm UTC
Hello Tom,
I have read your thoughts on schema design in your book. It gave me a new insight on schema design. Can you please suggest some MUST READ books and on-line resources for following:
1. Relational database design.
2. Dimensional database design
Thanks,
Arvind
Surrogate Key for Weak Entity
Sameer Kumar, September 15, 2010 - 10:35 pm UTC
Tom,
I agree for an environment where value of a Candidate Key/Business Key can change over a period of time, we should better have a surrogate key as primary key.
But if we have a scenario where we have an identifying relationship b/w strong entity-weak entity, shall the foreign key from strong entity be made primary key as well in weak entity (dependent table)? Or we should have a surrogate primary key?
Here we will be unnecessarily maintaining two indexes, two columns whereas joins will always be performed on (unique one-one identifying) foreign key. I somehow feel that the better approach in these scenario is to do away without surrogate key and use business key (identifying foreign key) as primary key. Kindly explain if you feel otherwise.
September 16, 2010 - 6:44 am UTC
Use natural keys when natural keys exist.
Use surrogate keys only when there is no true primary key.
... But if we have a scenario where we have an identifying relationship b/w strong
entity-weak entity, shall the foreign key from strong entity be made primary
key as well in weak entity (dependent table)? ...
that doesn't make sense. What does a foreign key in the 'strong' (parent) entity have to do with the child, the child has the foreign key to the parents primary key.
If you are talking about a one to one relationship, you really meant to use a SINGLE TABLE.
Surrogate Key for Weak Entity
Sameer Kumar, September 03, 2012 - 5:36 am UTC
Hi Tom,
Getting back to this old question from me...
You said
I would use a surrogate key here -- I almost always use surrogates personally.
and you said
Use natural keys when natural keys exist.
I little confusing.
that doesn't make sense. What does a foreign key in the 'strong' (parent) entity have to do with the child, the child has the foreign key to the parents primary key.
May be I used the words in wrong way. I mean a column in weak entity references a key in parent and the reference can uniquely identify the tuple in weak (child) entity as well.
If you are talking about a one to one relationship, you really meant to use a SINGLE TABLE.
What i mean here is suppose, I have a LOANS table and a table for LOANCHARGES. LOAN and LOANCHARGES though are one-to-one, but only LOANCHARGES are optional and are required only when there are charges associated.
Mmoreover the read services always read just the LOAN details or just the LOANCHARGE details and posts the charges accordingly. Now for this case I prefer creating a new table for LOANCHARGE (instead of storing it with LOANS)
I would do this to avoid storing too many nullable columns and avoid cases when I will have to query "select * from loans where chargeRelatedColumn is null". Now I can just say "select * from loancharge".
In this scenario I will prefer to use LOANID itself as PK in both the tables (instead of using a surrogate key in LOANCHARGE).
I had a discussion with people over this and thought otherwise, pointing me to your link they suggested, we should "never" use natural keys, specifically in case of master-detail/parent-child tables.
It will be nice if the users posting review/response get an email when your post a followup. :-)
If it is already there, I need to check my spam filter.
September 10, 2012 - 6:58 pm UTC
use natural keys when they exist... they do not exist frequently, hence I use surrogates most of the time.
What i mean here is suppose, I have a LOANS table and a table for LOANCHARGES. LOAN and LOANCHARGES though are one-to-one, but only LOANCHARGES are optional and are required only when there are charges associated.
then I would say that there should almost certainly be one table.
I would do this to avoid storing too many nullable columns
what is the scientific, numerate reason behind that? especially since if they are last in the table, they take zero bytes of storage?
avoid cases when I will have to query "select * from loans where chargeRelatedColumn is null"
again - why? we can index that so it would use an index if it made sense. Otherwise we'll just full scan it...
ask those people for the math or scientific reason behind it.
but what does a 1:1 relationship and whether it should be flat in a table or in two tables have to do with surrogates at all??
Sameer Kumar, September 22, 2012 - 1:59 pm UTC
Well some people had referred me to your post and told me that, we should (always) use surrogate keys.
Even when there is a natural key. I guess I got carried away in giving an example/explaining the scenario but got my point clarified that, "use natural keys whenever possible"