Db design advice

  • Hi All,

    This question is related to database design approach and this needs some system background. need some help here.
    This is very much similar concept upon how github or TFS works. (get latest version etc...)
    We have an existing system which has some tables.
    Here there are 2 concepts.

    Publish and Sync processes.
    ======      =====
    What happens in Publish.

    users can login to application and makes some changes or create some content which is end user will input some fields in the front end app. These changes are named as Working changeset. Next, Working changeset can be published to Preview state and from there it can move it Production state.
    Basically there is table called "Items". every time u work with some changes(also called changeset) and promote to next level
    i.e. from working set -> preview -> Production, a new row with the same content is again copied/re-inserting into the same table with a different changesetid and changeset type ( working changeset, preview changeset , production changeset) and latest production changeset data is consumed
    by another .net application using an API call. again, for better maintenance sake, once the production changeset is ready all the old preview changesets are deleted ( since they are of no use). note: we can have multiple Preview changesets but only 1 working changeset.
    what it means is, end user can make multiple changes and move it preview state and all the changesets present can be moved as a single unit into production state.

    What happens in sync ?
    They have a concept called bases. We have a separate table for this. Think of a base as one country( US, UK, Singapore, India etc...)
    By default, all bases have a parent base called "International".
    We can move changes across parent base and child base. by default, there will some end users and each enduser belong to a certain base.
    once he log's into the application, he can create the cotent and publish it. once publish is done, we can login to another base and he has the option of syncing/downloading the content from Parent base.
    When we are doing so, again a copy of the same rec (i.e. latest production changeset ) is copied again into the same table i.e. "Item"
    with different status columns. This time the status column would be "C" which indicates that it has copied content from its parent base.

    First Question
    =================

    Do we find any issue with this design approach?? because i see deadlocks when Publish and sync is run simulatenously.
    publish, will be adding a new row and Sync will also be adding a new row with different value in "Status" column.
    What is the better approach of implementing this requirement?

    Second question
    =================
    now a new requirement called hotfix to a production copy.
    This is a new requirement and should fit into current database model.
    This time we are skipping working changeset, then promote to --> preview changeset(s) --> and then moved to production changeset.
    and finally the production changeset is consumed by 1 or 2 downstream applications.

    Imp note:
    ==========
    Cx wants to apply on direct production changeset itself so no more extra steps required only production copy and hotfix changeset and
    thereby generate a new production changeset. no more working set and preview changesets. we now play with only production copy and apply missing changes/content and generate a new production copy changeset.

    Now, my question is, for the new hotfix requirement can use the existing tables or any new tables/columns needs to be added??? need some database design guidance around here.

    Current table structures
    ===================================

    CREATE TABLE [Bases](
        [Id] int NOT NULL,
        [Name] [varchar](200) NULL,  -- International base, USA base, UK base etc...
    CONSTRAINT [PK_Bases] PRIMARY KEY CLUSTERED
    (
        [Id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO

    CREATE TABLE [Changesets](
        [Id] int NOT NULL,
        [Title] [nvarchar](200) NULL,
        [BaseId] int NOT NULL,
        [ChangesetGroupId] int NULL,
        [Description] [varchar](4000) NULL,
        [LastItemUpdatedDate] [datetime2](7) NULL,
        [PublishToPreviewBy] [nvarchar](200) NULL,
        [PublishToPreviewDate] [datetime2](7) NULL,
        status varchar(10), -- 'W-working set, P-preview','
        
    CONSTRAINT [PK_Changesets] PRIMARY KEY CLUSTERED
    (
        [Id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    )
    GO

    CREATE TABLE [Items](
     id int not null,
        [ItemId] int not null,
        [Title] [varchar](200) NULL,
        [Status] [varchar](1) NULL,    
        [ChangesetId] int NOT NULL,
        [PreviousChangesetId] int NULL,
    CONSTRAINT [PK_ItemVersions] PRIMARY KEY CLUSTERED
    (
        [Id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
    CONSTRAINT [AK_ItemVersions_ChangesetId_ItemId] UNIQUE NONCLUSTERED
    (
        [ChangesetId] ASC,
        [ItemId] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    )
    GO

    ALTER TABLE Items WITH NOCHECK ADD CONSTRAINT [FK_ItemVersions_Changesets_ChangesetId] FOREIGN KEY([ChangesetId])
    REFERENCES [Changesets] ([Id])
    ON DELETE CASCADE
    GO

    ALTER TABLE Items WITH NOCHECK ADD CONSTRAINT [FK_ItemVersions_Changesets_PreviousChangesetId] FOREIGN KEY([PreviousChangesetId])
    REFERENCES [Changesets] ([Id])
    GO

    Thanks,

    Sam

  • Lots of information there. I'll try to make some suggestions.

    First, instead of copying all the data every time there's a promotion from one state to another, I'd rather see a state table that has begin and end for any given state. Then you just update an existing row and add a new row as the state changes. This assumes of course that the data itself isn't changing as the state changes. If we're looking at literally versioned information, where both the data and the state are changing, then, of course, you do need to have that copy (although it's not a copy if it's new data). I'm unclear why you'd be seeing deadlocks there (code and deadlock graph would be necessary to understand that bit).

    As for the second question, yeah, I suppose it will work the same way. Promoting from one state to another is promoting from one state to another.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Deadlocks are because they can run Publish and Sync simultaneously and same tables gets referenced using Foreign keys and update operations. Thats why seeing deadlocks.

  • If you're getting deadlocks from the process, I'd double check the query tuning first of all, the indexing next, and the calls made by the queries. If it's the same process with discrete calls in the same order, you really shouldn't be getting deadlocks. However, again, I'm still back to where I was. If the status is changing, that shouldn't require a complete copy of all data, rather, just a new status. Is it consistent on which process is chosen as the victim in the deadlocks?

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply