Re: 3 dimensional Oracle database

John F. Regus wrote:

> Okay how do I stack the cells from different sources all pertaining to the
> same subject but in different formats, ie. .doc or .txt, audio, and .bmp or
> .wmf? All of which were captured at the same time but by different
> departments.
>
> I know you will probably say well create three columns across a row to
> contain the data from the different sources. But what do I do about keeping
> the historical data along with the present data, because the present data
> will not be replaced when newer information comes along. Why? The
> historical data and most recent data will be used for statistical
> probability patterns.
>

What you are describing sounds like a fairly common data warehousing requirement - a fact table (in your case subject ?) with a number of dimension tables that pertain to the subject (from your postings, I'm guessing source, time, and media asset ?). The archetypal requirement in DW is commonly a sales "fact" table, "dimensioned" by time, product and   customer. You may want to research a little into a common technique used to represent these types of requirements - the simple star schema, and it's more complicated bigger brother, the snowflake schema. You should google these terms and see if they are applicable to what you want to do.

Note that this technique is not specific to an Oracle database - you can use it with any relational system. However, Oracle has some specific processing capabilities that will allow very efficient queries against these types of schemas - in particular look for bitmapped optimized star queries, and also bit mapped join indexes. Oracle also provides very strong data partitioning capabilities so that you can manage the loading and archiving of data very easily in this type of schema - perhaps range partitioning by time, with a corresponding usage of list sub partitioning to deal with data coming from different sources. You will also get data elimination in a query of the fact table if your partitioning key is based on the primary key you use for each of your dimension tables.

Note that this structure is not limited to 3 dimensions. In fact, any number of dimensions can be modelled with this type of design. It does get a little complicated however when time (i.e history) becomes a major part of any query, especially if the attributes of the fact table or any dimesnion table changes over time (i.e changes in product SKU). However, these complications can typically be deisgned out. Received on Sun May 22 2005 - 17:44:03 CDT