Using Bitwise
-
I am designing a database for a company that requires the details of all their members held. These members are then grouped according to criteria eg Male, Female, School, transactions over £25, under 16, over 65 etc.etc. There will be hundreds of groups, and new ones defined as we go along. I thought about using views to define the groups.
The company want to know for each member which groups they are in. If I stored this in a table with one row for each member, for each group, eg
memberno
group
then this table would be huge, as one member could belong to over 100 groups.
I thought about using bitwise to store the info, but I think an integer column will only hold 30 or so different groups as bitwise, and this is not enough. Plus it needs to be able to have groups added to it.
I was wondering if anyone else has a better idea on how to store this information, or should I just use a massive table.
-
The biggest bitwise mask you could create with numbers is 127 possible values using decimal/numeric type with precision of 38 and scale of 0. Here is code to generate those possiblities.
SET NOCOUNT ON
GO
CREATE TABLE vals (
vals numeric(38,0) not null primary key
)
GO
DECLARE @y AS NUMERIC(38,0)
set @y = 1
while @y < 99999999999999999999999999999999999999
begin
insert vals (vals) values (@y)
set @y = @y * 2
if @@error != 0
goto exithere
end
exithere:
SELECT COUNT(*) FROM vals
GOOr you could create a char of upto 8000 possibilites by using 0 and 1 string value. Then you could write a method to calulate the mask but it would be a bit complicated. As this is the first time I have thought of using char (or varchar) I have not code for demonstration.
-
Nevermind it hit me just now, so here is an example that cross breeds int with varchar to handle bitmask via substring position to mark it.
SET NOCOUNT ON
GO
CREATE TABLE concept1 (
bitpos int not null primary key,
val varchar(40) not null
)
go
insert concept1 (bitpos,val) values (1,'Under 16')
insert concept1 (bitpos,val) values (2,'Over 65')
insert concept1 (bitpos,val) values (3,'Male')
insert concept1 (bitpos,val) values (4,'Female')
insert concept1 (bitpos,val) values (5,'transactions over £25')
insert concept1 (bitpos,val) values (6,'transactions under £25')
GO
CREATE TABLE concept2 (
idx int identity(1,1) primary key not null,
nickname varchar(10) not null,
bitmask varchar(100) not null
)
go
INSERT concept2 (nickname, bitmask) values ('Antares686', '10101')
INSERT concept2 (nickname, bitmask) values ('Gabby', '10011')
INSERT concept2 (nickname, bitmask) values ('Leroy', '011001')
GO
SELECT
concept2.NickName, concept1.val
FROM
concept2
INNER JOIN
concept1
ON
substring(concept2.bitmask,concept1.bitpos,1) = 1
GOOne thing to note is I did the mask as left to right instead of right to left. You could still store right to left if you prefer and use REVERSE inside the SUBSTRING call on th bitmask field to still work it out.
Edited by - antares686 on 04/25/2003 04:39:18 AM
-
Some years ago I has done a similar using a char(500) to managed permissions within an application. It is definitely nothing I want to do again. But at that time we were under time pressure and that was an easy way to achieve our goals.
You must put a lot of brain work in programmatical logic to get things right. But it works, so I'm gladly do not have to change this. If this is ever to be redesigned I will use tables and subtables and subtables.... That means a lot of thinking on database design, but that's better.
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply