Re: DB Design question

"Naran Hirani" <N.Hirani_at_hgmp.mrc.ac.uk> wrote in message news:ba10co$pdi$2_at_niobium.hgmp.mrc.ac.uk...
> I hope this is not off topic for this NG.
>
> My colleague is trying to achieve the following two difficult tasks and
> would appreciate any input you guys can provide.
>
> 1. He needs to store large numbers of user attributes in a table.
> In other words, for each user (say 15000+) he needs to store
> many thousands (potentially) of flags. Each flag itself is small -
> either a character, or simple boolean, or a number, but there are
> arbitrary (ie. unknown) numbers of these flags, and the number of flags
> is extremely large.
>
> What is the best way to store such data?

If they are arbitrary then you need some sort of identifier/type associated with them as well. One could do one of a few things here are 2 that come to mind immediately:

--Let's say the user looks like this
Create Table Users (

    UserID int identity(1,1) -- for lack of a natural key in this example , Name varchar(50)
)

• Then the attributes could look like this: Create Table Attributes ( UserID int , AttributeDescription varchar(50) -- , AttributeType int -- to convert the value to a usable one , AttributeValue varchar(10) -- could store an integer, boolean or integer value )

--OR: ...

Create Table Attributes (

    UserID int
, AttributeDescription varchar(50) --

,   AttributeType            int                 -- Know which field to read
back
,   AttributeValueInt       int
,   AttributeValueChar    char(1)
,   AttributeValueInt       Boolean

Then getting all the attributes for a given user would be a simple join, or if you wished to have it in a pivotted tabular form, then you would have to know which specific attributes you wanted.

• B