Building table of unique sets

What I’m trying to do is build a table of unique sets of rows.

What is the aim?

I’m building an app for a service provider (let’s say cable company for example). Every customer has a set of services on his connection. Every customer can have different number, type and combination of services installed.

For example some customers pays for standard cable package, they pay for HBO extra package and they also pays for online movie storage…

The system I’m building will allow the admin to create a migration. Each migration has groups and inside every group there is a matrix of service changes. Point is that the service provider is changing his portfolio and needs to migrate all the customers with certain combinations of services to different combinations.

Here’s my SQL DDL code:

CREATE TABLE [Migration]
(  
    [MigrationId]   INT NOT NULL IDENTITY(1,1)
    [Name]          VARCHAR(50) NOT NULL,
    [Description]   VARCHAR(250) NOT NULL
)

ALTER TABLE [Migration] 
    ADD CONSTRAINT [Migration_pk] 
        PRIMARY KEY CLUSTERED ([MigrationId]) 
                    WITH (ALLOW_PAGE_LOCKS = ON , ALLOW_ROW_LOCKS = ON ) 

CREATE TABLE [Migration_Service_Group]
(
    [GroupId]       INT NOT NULL IDENTITY(1,1)
    [MigrationId]   INT NOT NULL
)

ALTER TABLE [Migration_Service_Group] 
    ADD CONSTRAINT [MigSerGroup_pk] 
        PRIMARY KEY CLUSTERED ([GroupId]) 
                    WITH (ALLOW_PAGE_LOCKS = ON , ALLOW_ROW_LOCKS = ON ) 

CREATE TABLE [Migration_Matrix]
(
    [MatrixId]      INT NOT NULL IDENTITY(1,1), 
    [GroupId]       INT NOT NULL
    [ServiceType]   INT NOT NULL,
    [OldRefId]      INT NOT NULL,
    [NewRefId]      INT NOT NULL
)

ALTER TABLE [Migration_Matrix] 
    ADD CONSTRAINT [MigMatrix_pk] 
        PRIMARY KEY CLUSTERED ([MatrixId]) 
                    WITH (ALLOW_PAGE_LOCKS = ON , ALLOW_ROW_LOCKS = ON ) 

ALTER TABLE [Migration_Service_Group]
    ADD CONSTRAINT [MigSerGroup_fk] 
        FOREIGN KEY ([MigrationId]) REFERENCES [Migration]([MigrationId])
                ON DELETE NO ACTION 
                ON UPDATE NO ACTION

ALTER TABLE [Migration_Matrix]
    ADD CONSTRAINT [MigMatrix_fk] 
        FOREIGN KEY ([GroupId]) REFERENCES [Migration_Service_Group]([GroupId])
                ON DELETE NO ACTION 
                ON UPDATE NO ACTION

What I don’t know how to do is enforcing this rule:

Every group is unique => every set of rows in table Migration_Matrix with GroupID is unique, so that there are no same or contradicting groups of rules for migration.

For example: Group 1 (Migration_matrix => Migration_service_group, GroupId:1) has these records:

1,1,1,145,156 
2,1,1,175,168

If Group 2 (Migration_matrix => Migration_service_group, GroupId:2) started like this, it would be error:

3,2,1,145, .... 
4,2,1,175, ....

Because it contradicts the first group. But if group2 had an extra rows (another service change) it should be OK.

Any idea how to enforce it on the database side?

Answer

This type of constraint is better served on the app side. When a user starts to create/modify a matrix for a group the app locks the group. When a user hits Save the app checks constraints, updates the db and unlocks the group.

Advertisement

Answer