Combining Database IDs

There was a interesting requirement recently that came up.  It was to basically modify and existing BIT field and ALTER it to be an INT field.  Essentially it was to really push the limits of an INT field. 

Traditionally in a relational database, you have a one-to-one mapping of PK-to-FK (primary key to foreign key).  The goal here is to combine values that representative of the object in that database row.  In this case we are talking about permissions.

So first we need to create the table that will generate the IDs.  We need a seed which will be used as a basis of our ID.  We want to use binary values, it will be a power of two (2) for the primary key ID.

CREATE TABLE [Permissions]
(
     [Seed] [smallint] IDENTITY(-1, 1) NOT NULL,
     [Id] AS (power((2),[Seed])) PERSISTED NOT NULL,
     [PermissionName] [varchar](255) NOT NULL,
CONSTRAINT [PK_ID] PRIMARY KEY CLUSTERED ([Id] ASC),
UNIQUE NONCLUSTERED ([Seed] ASC)
)

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 

Now that we have a table that can generate these IDs, we need to populate.  Below is a sample script that will insert some values into the table.

INSERT INTO [Permissions] (PermissionName) VALUES ('None')
INSERT INTO [Permissions] (PermissionName) VALUES ('Read')
INSERT INTO [Permissions] (PermissionName) VALUES ('Write')
INSERT INTO [Permissions] (PermissionName) VALUES ('Execute')

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 

Let’s run a SELECT * and see what happened.

image

As you can see we have some nice values to work with.  I’ll show you what I mean.  Traditionally you would do some kind of join and then determine the value.  In this case, we are going to leverage the power of the bitwise operators built into SQL Server.  I’m going to write a stored procedure that sets my permissions.  Let say that I have a user table and in that table I have a field INT called PermissionId.  I can set my PermissionID using the stored proc or in a simple UPDATE statement.

UPDATE Users SET PermissionId = 3 WHERE [UserId] = 1

What I have basically said is for userid = 1, give them Read and Write permissions.  Let’s validate that. using bitwise operators.  We will exclude 0, because None will be included every time.

DECLARE @Permissions varchar(MAX)
DECLARE @PermissionId int 
 
SET @PermissionId = 3
 
SELECT @Permissions = COALESCE(@Permissions + '/', '') + PermissionName
FROM Permissions
WHERE Id = Id & @PermissionId
AND Id <> 0
 
PRINT @Permissions

 

This returns ‘Read/Write’.  Let try the same for 5.  It returns ‘Read/Execute’.  How about for 7. Yup, just what I thought, ‘Read/Write/Execute’.

Technorati Tags: ,,,

Leave a Reply