Database schema

Brief post as I'm tired this evening. In an upcoming project, the users have requested that we stage their edits. Lead dev and myself have had similar experiences where users request to stage their data, wonderfully complex workflow engines are built and after a short period in production, they ask for the big red button to mass approve all changes.

The purpose of tonight's post is to address one of the concepts they are kicking around. Instead of having staged and live data in the same tables with an indicator (flag, publish date, whatever) they were considering having two separate databases with identical schemas to support this. They would create different user accounts for accessing the data to make sure only blessed content is served to the public. Without knowing the details, I can see why separate databases would be considered but I also mentioned that it might be an opportunity to use schemas to logically partition the database. They could still create the different accounts for access but for database backups and restores, especially as developers roll on and off the project, I thought there might be some merit to the schema approach.

The following code will create a sample database. Within that database, it will create a live and stage schema and the same table within each (works). I will load sample data into the stage version and use a stored procedure to simulate the approval of content for the productional site. TSQL is unpolished but it gets the point across.

What's not shown is the creation of user accounts and assignment of accounts/roles to schemas. It's late and unfortunately for me, I am not fluent enough in the security aspects of SQL Server to generate the TSQL without digging in to BOL.

CREATE DATABASE SCHEMAD
go
USE schemad
go
create schema stage
GO
create schema live
GO

CREATE TABLE stage.WORKS
(
    works_id int identity(1, 1) NOT NULL PRIMARY KEY
,    message_text varchar(max) NOT NULL
,    name_last varchar(50) NULL
)
GO
CREATE TABLE live.WORKS
(
    works_id int identity(1, 1) NOT NULL PRIMARY KEY
,    message_text varchar(max) NOT NULL
,    name_last varchar(50) NULL
)
GO
CREATE PROCEDURE
    stage.Publish
(
    @works_id int
)
AS
BEGIN
    SET NOCOUNT ON
    
    INSERT INTO
        live.WORKS
    (
        message_text
    ,    name_last
    )
    SELECT
        W.message_text
    ,    name_last
    FROM
        stage.WORKS W
    WHERE
        W.works_id = @works_id
    
END
GO
-- load up some staging data
INSERT INTO
    stage.WORKS
(
    message_text
,    name_last
)
OUTPUT INSERTED.*
SELECT
    'My fingers emit sparks of fire with expectations of my future labors' AS message_text
,    'blake' AS name_last
UNION ALL
SELECT
    'The mind can make a heaven out of hell and a hell out of heaven' AS message_text
,    'milton' AS name_last
UNION ALL
SELECT
    'If the fool would persist in his folly he would become wise' AS message_text
,    'blake' AS name_last
UNION ALL
SELECT
    'Excess of sorrow laughs. Excess of joy weeps' AS message_text
,    'blake' AS name_last

GO
-- publish some blake
EXECUTE stage.publish 1
EXECUTE stage.publish 3

GO
SELECT * FROM stage.works
SELECT * FROM live.works

GO
/*
-- cleanup
drop table live.works
drop table stage.works
drop procedure stage.publish
drop schema stage
drop schema live


*/