"John F. Regus" <jfregus (AT) ix (DOT) netcom.com> wrote in
news:TP6ke.1380$oT1.1370 (AT) newsread1 (DOT) news.pas.earthlink.net:

I'll stipulate that real time data can be recorded or stored in a DB.
At any moment past that point, and whenever it is queried, it's historical!
The data shows what reality was when the data was stored; even if only one
second has elapsed since the COMMIT occurred. I am not saying the data is
incorrect or invalid. I am saying that if ANY time has passed since the
data was recorded, you now are looking at historical data.

Cells? What cells. A database is NOT a spreadsheet.

CREATE TABLE RAW_SOURCE (
SUBJECT_ID VARCHAR2(255),
DATE_TIME_CREATED DATE,
SOURCE_TYPE VARCHAR2(31),
-- where SOURCE_TYPE indicates TYPE of data; doc, txt, audio, bmp, etc.
-- ADD ANY OTHER COLUMNS DEEMED USEFUL & NECESSARY
SOURCE_DATA BLOB);

I contend that a table similar to the one above meets ALL
your requirements. This table can store data on multiple subjects.
It stores multiple formats of data. It stores "current" & historical data.

If you want to call this a 3-Dimensional table, knock yourself up!


From your persepective how do you EXPLICITLY differentiate "current" data
from historical data?

When does "current" data morphs into historical data?
Who, what, how does "current" data transform into historical data?

John F. Regus wrote:

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.

I use the term "cell" to indicate the intersection of a row and column. Is
this not the correct term. I define real time as today, and historical as
yesterday, but that does not mean that additional information in the cell
cannot be updated again later in the day, and then the previous data is
historical.

But I don't see how to retrieve what would be a subscripted row/column
intersection.


"IANAL_VISTA" <IANAL_Vista (AT) hotmail (DOT) com> wrote

"John F. Regus" <jfregus (AT) ix (DOT) netcom.com> wrote in
news:dV9ke.1491$oT1.385 (AT) newsread1 (DOT) news.pas.earthlink.net:

JFR was told more than once TOP POSTING is NOT acceptable.

Obviously JFR can't even spell SQL;
let alone know what SQL is or how to use SQL.

When your only tool is hammer, all problems are viewed as nails.
When a 3GL prgrammer is faced with a database, all solutions
MUST be implemented as arrays.

You're On Your Own (YOYO)!

A shovel is a great tool for making a hole on the ground;
but only when the correct end of the shovel comes into
contact with the Earth. You're using the wrong end of the DB!

You're problems would be more easily solved if you used a muave database
instead of a multidimensional database.

*PLONK*

John F. Regus wrote:
I agree with IANAL_VISTA. We have asked you politely not to top post and
you ignore the simple request for courtesy so YOYO.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)

John F. Regus wrote:

The simplistic answer is that in a relational database you cannot.

However, I am 100% sure you can model what you actually want to do in a
relational database.

Can you try to describe the problem you are trying to solve at the
business level, because I am sure there are viable solutions other than
the percieved need for a subscripted row/column intersection.

What is wrong with the table definition you have been given so far ? It
seems to me that it does everything you need for the problem so far
described.

CREATE TABLE RAW_SOURCE (
SUBJECT_ID VARCHAR2(255),
DATE_TIME_CREATED DATE,
SOURCE_TYPE VARCHAR2(31),
-- where SOURCE_TYPE indicates TYPE of data; doc, txt, audio, bmp, etc.
-- ADD ANY OTHER COLUMNS DEEMED USEFUL & NECESSARY
SOURCE_DATA BLOB);

"IANAL_VISTA" <IANAL_Vista (AT) hotmail (DOT) com> wrote:


Doesn't the purple one have the most RAM?



Paul...


--

plinehan __at__ yahoo __dot__ __com__

XP Pro, SP 2,

Oracle, 9.2.0.1.0 (Enterprise Ed.)
Interbase 6.0.2.0;

When asking database related questions, please give other posters
some clues, like operating system, version of db being used and DDL.
The exact text and/or number of error messages is useful (!= "it didn't work!").
Thanks.

Furthermore, As a courtesy to those who spend
time analyzing and attempting to help, please
do not top post.