Re: Theoretical Design Question

Oz Ben Eliezer wrote:
>
> Hey!
>
> My question is as follows...
>
> I need to store items in my database. I want the database to be able to
> handle up to 1,000,000,000 items. I'll explain what I mean by "handle" in a
> moment.
>
> Each item has its own properties. Each property is represented by a 0-9
> number. I want each item to have several thousands of different properties.
>
> Now I'll return to the "handle" thing. I want the system to perform
> comparisons and calculations on the items' properties, according to
> hardcoded rules defined by me, in the mission of finding the best suitable
> items for a specific requirement.
>
> For instance, suppose I have a "book" item which has 3 properties: weight
> (3), difficulty of reading (6) and prize (2). I'm trying to locate best
> suitable itemsb for weight(2), difficulty(6) and prize(3). So the system
> basically needs to go over all the items and calculate a value of
> "compatibility" for each one, choosing the most appropriate one (or several)
> at the end.
>
> I hope I was clear enough...
>
> How would you implement that? I forgot to mention that such queries are
> common enough to occur every millisecond. Would you use an SQL Server to
> store the data structure just described? Should I create a database system
> of my own to handle these data manipulation requirements? If you would
> implement that using an SQL Server - how would you do that? If otherwise -
> do you have some suggestions about how to design this data structure outside
> of the database? Maybe references to commercial software handling with those
> things?
>
> Thanks for your help!
> Oz

        Given the size of your database and the frequency of queries you should use an RDBMS such as (in alphabetical order) DB2, Informix, or Oracle. I
mention these because each can point to installations with billion row tables.

        You haven't provided sufficient information to allow your question to be answered without a lot of assumptions being made.

        For example when the database is queried is the user looking at 1 billion items or some identifiable subset or a specific item?

        Are the properties for each item in a subset fixed or are they subject to frequent change?

        If, for example, the user is querying one specific item and wants to know if it is suitable I would design a two table structure, one for items and one for properties and calculate the suitability on the fly.

        If the user is querying all of the items in the database and the properties are relatively static I would look into a three table structure with one table for items, one table for properties, and one table for precalculated suitability matches (even though the suitability table could have several rows for each item).

hth

-- 
Jerry Gitomer
Once I learned how to spell DBA, I became one.