I've read a lot from both camps in the surrogate vs. natural key debate. What I am struggling to understand, and what seems to be glossed over, especially in explaining how surrogate keys are more efficient, is how do referencing tables resolve a surrogate key?
Say, I am using an ORM in an application, and have a
userID in an application object. now we want to insert into a
SelectedTask table and a
SubTask table that are defined as follows (ignore the awkwardness of the real word example):
CREATE TABLE SelectedTask( userID INT References User(userID) taskNumber INT, name TEXT , jobSiteID INT References Site(jobSiteID), PRIMARY KEY (???) ); CREATE TABLE SubTask( assignee INT References User(assignee), subTaskID INT References Subtasks(subTaskID), timeAllowed INT, reportTo INT References Group(reportTo), -- how do we reference SelectedTask? );
If we use a surrogate key for the
SelectedTask table, how do we use that in the
SubTask table? If we use natural keys, the primary key could simply be a compound primary key of
taskNumber, and we could insert into both at the same time assuming we have programmatically gathered all of the pieces.