Take the 2-minute tour ×
Code Review Stack Exchange is a question and answer site for peer programmer code reviews. It's 100% free, no registration required.

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?

share|improve this question
1  
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'). –  Clockwork-Muse Nov 19 '12 at 21:26
add comment

1 Answer

up vote 1 down vote accepted
  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.

share|improve this answer
    
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. –  Gene S Nov 20 '12 at 15:12
    
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 –  almaz Nov 21 '12 at 10:58
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.