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?

Without any knowledge of how these flags will be used, which applications are most time-critical etc., it is impossible to say what the best way to store them is. When you say there are unknown numbers of these flags, I suspect the relationally correct way to represent them is with a relation per flag or per small handful of closely associated flags. If you want to physically cluster all of the flags per user, I don't think you will find any products with sufficient physical independence.

If these are stable user-attributes, you may want to investigate a directory service like LDAP.

> 2. Every minute his program measures how busy a system is and then he
> only wants to store the _last_ 50 of these measurements.

SQL does not provide any concise quota query operation and the possible SQL solutions are quite complex as a result. A better relational language would simply have a quota query operation or at least some other partition operation.

I seem to recall that Dennis Shasha and Philippe Bonnet recommend kdb for similar problems in their performance tuning book. While I don't like kdb's changes to the logical model, if it provides an immediate solution, you may just have to hold your nose and choke it down.

> Can anybody suggest a good way to tackle the above two problems.
>
> The things that he and I have come up with seem clumsy and would lack
> scalability.
>
> Many thanks, I look forward to your comments and suggestions.
>
> Naran.
>
Received on Fri May 16 2003 - 00:07:26 CEST