Hello,
I have 300 groups. Each user can or can not be associated with certain
group. I would like to represent group membership using corresponding flag
(on/off). Using integer dattatype it will look like : G1- 1, G2 - 2, G3 -
4....
If user is member of group of 1,2 and 3 it will be represented by number 7,
etc.
It is power of 2.With BIGINT data type I can only store up to 63 different
groups. I need to store up to 300 groups.
I could split groups into several integer fields but I was hoping to use
single VARBINARY field.
I am trying to store all flags in VARBINARY(300) field. The problem is
that when populating groups table (see below), SQL server can only perform
bitwise operations when one of the agruments is of integer type. I am trying
to loop through all 300 groups and populate them with power of 2.
I run into the same limitation (no more than 63 groups). Is this possible
to store 300 flags for each groups ina single field?
Thanks,
Igor
CREATE TABLE groups_bin
(
group_id SMALLINT ,
group_name VARCHAR(100),
group_bit_mask VARBINARY(2)
)
DECLARE @.binBitMask VARBINARY(255),@.intID INT,@.intMaxID INT
SELECT @.binBitMask=0x0001,@.intID=1,@.intMaxID=30
0
WHILE @.intID<=@.intMaxID
BEGIN
INSERT INTO groups_bin(group_id,group_name,group_bit
_mask)
VALUES(@.intID,'G'+CAST(@.intID AS VARCHAR(30)),@.binBitMask)
SELECT @.binBitMask=CAST(@.binBitMask*2 AS VARBINARY(255)),@.intID=@.intID+1
PRINT CAST(@.binBitMask*2 AS VARBINARY(255))
ENDArgh you are seriously, seriously trying to completely denormalize your
data? Have you considered the problem of all the logic involved with
updating a single flag for an individual user in the future? Or even all
the logic involved in just extracting a single flag setting for an
individual user? You've already run into one of the limitations of the
software - the physical limitation of the number of bits a certain field
type can hold. You're relying on what you perceive to be the observed
physical design of the system - nothing says that this won't change down the
road...
This was a very poor design decision...
"Igor Marchenko" <igormarchenko@.hotmail.com> wrote in message
news:%23Mmhe%23AMFHA.2384@.tk2msftngp13.phx.gbl...
> Hello,
> I have 300 groups. Each user can or can not be associated with certain
> group. I would like to represent group membership using corresponding flag
> (on/off). Using integer dattatype it will look like : G1- 1, G2 - 2, G3 -
> 4....
> If user is member of group of 1,2 and 3 it will be represented by number
> 7, etc.
> It is power of 2.With BIGINT data type I can only store up to 63
> different groups. I need to store up to 300 groups.
> I could split groups into several integer fields but I was hoping to use
> single VARBINARY field.
> I am trying to store all flags in VARBINARY(300) field. The problem is
> that when populating groups table (see below), SQL server can only perform
> bitwise operations when one of the agruments is of integer type. I am
> trying to loop through all 300 groups and populate them with power of 2.
> I run into the same limitation (no more than 63 groups). Is this possible
> to store 300 flags for each groups ina single field?
> Thanks,
> Igor
> CREATE TABLE groups_bin
> (
> group_id SMALLINT ,
> group_name VARCHAR(100),
> group_bit_mask VARBINARY(2)
> )
> DECLARE @.binBitMask VARBINARY(255),@.intID INT,@.intMaxID INT
> SELECT @.binBitMask=0x0001,@.intID=1,@.intMaxID=30
0
> WHILE @.intID<=@.intMaxID
> BEGIN
> INSERT INTO groups_bin(group_id,group_name,group_bit
_mask)
> VALUES(@.intID,'G'+CAST(@.intID AS VARCHAR(30)),@.binBitMask)
> SELECT @.binBitMask=CAST(@.binBitMask*2 AS VARBINARY(255)),@.intID=@.intID+1
> PRINT CAST(@.binBitMask*2 AS VARBINARY(255))
> END
>|||Sorry, but you have a lousy design and the best thing you can do is get rid
of it. The standard way to represent a many-to-many relationship is called a
Junction Table or Joining Table. Something like this:
CREATE TABLE UserGroups (user_id INTEGER REFERENCES Users (user_id),
group_id INTEGER REFERENCES Groups (group_id), PRIMARY KEY (user_id,
group_id))
I recommend you study a book on data modelling if you aren't already
familiar with relational design principles.
David Portas
SQL Server MVP
--|||A design change would be best, however if you insist on doing something like
this you can take a look at SQL Server's own sprocs for handling roles and
users.
Check out the code in sp_addrole, sp_addrolemember etc. In the SQL Server
version it uses a varbinary(2048) field to store up to 16k of options.
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||No problem, David. The criticism is greately appreciated :) I am
familiar with relational design principals. The reason why I opted for
bitmask approach is because I am trying to save space. Every user can be
member of any/all of 300 groups (number of groups fixed). We can save a lot
of space if we can represent user's group membership with a single, for
example VARBINARY field. With traditional relational approach space
consumption would be higher.
We will have millions of users and I was hoping bimask approach will save
us a lot of space.
Please let me know if my thinking is incorrect.
Thanks,
Igor
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:zbidnU2EcNHB8t_fRVn-ow@.giganews.com...
> Sorry, but you have a lousy design and the best thing you can do is get
> rid of it. The standard way to represent a many-to-many relationship is
> called a Junction Table or Joining Table. Something like this:
> CREATE TABLE UserGroups (user_id INTEGER REFERENCES Users (user_id),
> group_id INTEGER REFERENCES Groups (group_id), PRIMARY KEY (user_id,
> group_id))
> I recommend you study a book on data modelling if you aren't already
> familiar with relational design principles.
> --
> David Portas
> SQL Server MVP
> --
>|||Rick, I am not 'insisting' on this design. My goal is to save space. We
will have millions of users. Number of groups is predefined and fixed. I see
no difference between representing several flags as Microsoft is doing in
system tables and 300 using bitmask. I think I might be able to save a lot
of space with this design. I can use bitwise logic to set/reset/verify
flags.
Is my thhinking incorrect?
Thanks,
Igor
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:uIIqJIIMFHA.1300@.TK2MSFTNGP10.phx.gbl...
>A design change would be best, however if you insist on doing something
>like
> this you can take a look at SQL Server's own sprocs for handling roles and
> users.
> Check out the code in sp_addrole, sp_addrolemember etc. In the SQL
> Server
> version it uses a varbinary(2048) field to store up to 16k of options.
> HTH
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||Michael,
I am trying to conserver space. I have no problems updating flags using
bitwise logic. In fact, I already have prototype working using BIGINT field.
Unfortunately, it only allows me to store 62 flags. I can easily
set/rest/extract users flags.
I was hoping to use VARBINARY to store 300 flags in a single field. We
have predefined/fixed set of groups.
Any comments are welcome.
Thanks,
Igor
"Michael C#" <xyz@.abcdef.com> wrote in message
news:jAp0e.7247$AF3.6586@.fe10.lga...
> Argh you are seriously, seriously trying to completely denormalize your
> data? Have you considered the problem of all the logic involved with
> updating a single flag for an individual user in the future? Or even all
> the logic involved in just extracting a single flag setting for an
> individual user? You've already run into one of the limitations of the
> software - the physical limitation of the number of bits a certain field
> type can hold. You're relying on what you perceive to be the observed
> physical design of the system - nothing says that this won't change down
> the road...
> This was a very poor design decision...
> "Igor Marchenko" <igormarchenko@.hotmail.com> wrote in message
> news:%23Mmhe%23AMFHA.2384@.tk2msftngp13.phx.gbl...
>|||Is every user a member of all 300 groups? On average, how many groups would
you say that your users belong to?
"Igor Marchenko" <igormarchenko@.hotmail.com> wrote in message
news:eiqaZ6JMFHA.2464@.TK2MSFTNGP10.phx.gbl...
> Michael,
> I am trying to conserver space. I have no problems updating flags using
> bitwise logic. In fact, I already have prototype working using BIGINT
> field. Unfortunately, it only allows me to store 62 flags. I can easily
> set/rest/extract users flags.
> I was hoping to use VARBINARY to store 300 flags in a single field. We
> have predefined/fixed set of groups.
> Any comments are welcome.
> Thanks,
> Igor
>
> "Michael C#" <xyz@.abcdef.com> wrote in message
> news:jAp0e.7247$AF3.6586@.fe10.lga...
>|||P.S. - While you could kludge this design and probably force it to work,
you're trading off maintainability, scalability and speed to save a little
space. Hard drive costs are pretty darn low :)
"Igor Marchenko" <igormarchenko@.hotmail.com> wrote in message
news:eiqaZ6JMFHA.2464@.TK2MSFTNGP10.phx.gbl...
> Michael,
> I am trying to conserver space. I have no problems updating flags using
> bitwise logic. In fact, I already have prototype working using BIGINT
> field. Unfortunately, it only allows me to store 62 flags. I can easily
> set/rest/extract users flags.
> I was hoping to use VARBINARY to store 300 flags in a single field. We
> have predefined/fixed set of groups.
> Any comments are welcome.
> Thanks,
> Igor
>
> "Michael C#" <xyz@.abcdef.com> wrote in message
> news:jAp0e.7247$AF3.6586@.fe10.lga...
>|||A couple of notes..
1. Having a join table and storage table is not *that* much bigger storage
wise.
2. Yes, you can use the bitwise logic to set/reset/verify the values
however it is not particularly fast in SQL Server.
On a table with 5 million rows (and a varbinary(2048)) I did an update that
set the bitflag for 100,000 rows or so. It took much more time than doing
an update across the joined tables.
So you might save some space, but you will lose out on speed.
Rick
"Igor Marchenko" <igormarchenko@.hotmail.com> wrote in message
news:O90hw4JMFHA.3080@.TK2MSFTNGP10.phx.gbl...
> Rick, I am not 'insisting' on this design. My goal is to save space. We
> will have millions of users. Number of groups is predefined and fixed. I
see
> no difference between representing several flags as Microsoft is doing in
> system tables and 300 using bitmask. I think I might be able to save a lot
> of space with this design. I can use bitwise logic to set/reset/verify
> flags.
> Is my thhinking incorrect?
> Thanks,
> Igor
>
> "Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
> news:uIIqJIIMFHA.1300@.TK2MSFTNGP10.phx.gbl...
and
>
