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
Is this the correct pattern to use for this scenerio or is there a better approach?
Is the MERGE command the appropriate approach to Insert/Update a record (I am using SQL Server 2008)?
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?
