none
Database design helo required

    Question

  • 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
    Monday, June 18, 2018 7:48 PM

Answers

All replies