Re: DB Design question

From: Naran Hirani <N.Hirani_at_hgmp.mrc.ac.uk>
Date: Fri, 16 May 2003 19:54:26 +0100
Message-ID: <ba3c7l$fag$1@niobium.hgmp.mrc.ac.uk>

Thanks, Absinthe. I am aware of the classical way in which to represent this sort of data, but my main concerns here were to do with the fact that this table (userTable) can get very large, typically: 15000+ users x 5000+ flags per user implies more than 75 million rows in the user table. Under these circumstance would I be able to achieve adequate performance in terms of data retrieval and updates? and even if this is ok initially, will it scale well?

I was therefore wondering if people use alternative storage strategies that might return better performance and scalability?

Naran.

Absinthe wrote:
> "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
>
>
Received on Fri May 16 2003 - 13:54:26 CDT