Review Insert/Update stored procedure

Question

I am not a DBA nor do I play one on TV.

My application is retrieving information from Facebook and storing it into our own database. I want to retrieve the Location Post information of Id and AuthorId. AuthorId is a foreign key to another table. I was told by a DBA that foreign keys should always use internal unique identifiers because you can never rely on external unique identifiers. So I created the following table structure...

CREATE TABLE tblAuthor
(
 [MyAuthorId] INT NOT NULL PRIMARY KEY IDENTITY
 [FacebookAuthorId] BIGINT NOT NULL
 ...Additional Facebook information...
)
CREATE TABLE tblLocationPost
(
 [MyLocationPostId] INT NOT NULL PRIMARY KEY IDENTITY, 
 [FacebookLocationPostId] BIGINT NOT NULL, 
 [MyAuthorId] INT NOT NULL,
 ...Additional Facebook information...
 CONSTRAINT [FK_tblLocationPost_Author] FOREIGN KEY (MyAuthorId) REFERENCES tblAuthor(MyAuthorId)
)

The data I get back from Facebook when querying Location Post is LocationPostId and AuthorId. So I have created the following stored procedure to insert/update the LocationPost information in the database...

CREATE PROCEDURE [dbo].[LocationPost_Save]
@facebookLocationPostId bigint,
@facebookAuthorId bigint
AS
BEGIN
 DECLARE @myAuthorId INT
 DECLARE @IdentityColumn TABLE ( IdentityId int )
 BEGIN TRANSACTION
 /* Locate out internal unique id using facebooks unique author id, insert it if necessary */
 IF EXISTS (SELECT * FROM tblAuthor WHERE FacebookAuthorId = @facebookAuthorId)
 BEGIN
 SELECT @myAuthorId = MyAuthorId FROM tblAuthor WHERE FacebookAuthorId = @facebookAuthorId
 END
 ELSE
 BEGIN
 INSERT INTO tblAuthor (FacebookAuthorId) VALUES (@facebookAuthorId)
 SELECT @myAuthorId = SCOPE_IDENTITY()
 END
 /* Insert/Update one row in LocationPost */
 MERGE tblLocationPost lp
 USING (SELECT @facebookId as FacebookId) fb
 ON (lp.FacebookId = fb.FacebookId)
 WHEN MATCHED THEN 
 UPDATE SET 
 MyAuthorId = @myAuthorId,
 ...Additional data...
 WHEN NOT MATCHED THEN
 INSERT 
 (FacebookLocationPostId, MyAuthorId, ...Additional Columns...)
 VALUES 
 (@facebookLocationPostId, @myAuthorId, ...Additional Values...)
 OUTPUT INSERTED.MyLocationPostId INTO @IdentityColumn;
 COMMIT TRANSACTION
 /* Return the identify column of Inserted/Updated row */
 SELECT IdentityId FROM @IdentityColumn
END

1. Is this the correct pattern to use for this scenerio or is there a better approach?

2. Is the MERGE command the appropriate approach to Insert/Update a record (I am using SQL Server 2008)?

3. Do I really need to put my own Author Unique Id in the LocationPost table, or can I simplify this by putting the Facebook Unique Id?

• Clockwork-Muse: Generally, the way I see the whole "don't allow external primary key" bit, is when an external entities are attempting to declare internal primary keys that your system has to manage. When you're essentially mirroring somebody else's system (as you are here), there's no point, because you have to use their primary keys to match up the records anyways (meaning, you'd essentially have two primary keys, with the same effect, you just 'trust' one 'less').

Answer 1

1. I'd rather use the FacebookAuthorId and FacebookLocationPostId as primary keys, because your logic ensures them to be unique. In case of your current solution you would still require (unique) indexes on these fields (in order for lookups to be efficient) so there is no reason not to use them as primary keys.
2. Given suggest optimization you don't need to check/insert an author record LocationPost_Save, just use the merge to insert/update the data in the table, and create a separate Author_Save SP that will do the same job for Authors.

Having said that, assuming that you are using one of the high-level languages :) and you're developing a long-living solution I would actually recommend you to use one of the ORMs available for your language to interact with the database. It'll take a bit longer at the beginning, but you'll get a more flexible solution in a long term.

• Gene S: On your note about ORM's. I have always heard they are not efficient because they do not precompile the T-SQL statements as stored procedures do. Is this no longer true? Our application could potentially have hundreds (maybe even thousands) of hits a second during certain times of day.
• almaz: You should not be worried about pre-compilation at this stage as it's an example of "premature optimization" (google this term). And answering your question - no, they don't suffer from not being pre-compiled because most DBMS (and SQL Server, of course) cache execution plans ("compiled T-SQL statements") and thus ORM-generated statements will still be as quick as stored procedures. You might require a tuning stage where you profile queries generated in order to optimize the performance where necessary, but that stage should be done after you've got real-world usage statistics