SQL for Smarties | SQL Programming Style | Trees and Hierarchies in SQL | SQL Puzzles and Answers | Data and Databases

## Tuesday, May 23, 2006

### Dimensional modeling problem: need advice from expirienced engeners

SQL Apprentice Question

1. I have large number (50000000 items) of objects with their
propreties
for example these object are some coins.

2. I also have large number (30000000 items) of object which are
related with previous
defined objects(coins). For example "coin collectors"

------------------------------------------------------
SO TILL NOW HE INTRODUCED 2 OBJECT TYPES LETS CONTINUE
------------------------------------------------------

3. I have listing of coins and it's owners every day. So it can be
said that I have snaoshoot of situation for EACH day.

Example:

DAY 1:
______
mike - coin#1235
mike - coin#156
.
.
David - coin#456
.
Peter - coin#78
.
.
.

DAY 2:
______
mike - coin#156
.
.
.
David - coin#456
David - coin#1235

Peter - nocoins

Samuel - coin#4

So we see that:
- some coins changed their owners
- some new coins are introduced
- some coins are destroyed
- some new owners are on the sceen
- some owners retired

-------------------------------------------------------------------------
SO TILL NOW WE HAVE REGULAR TRADING SITUATION HERE WITH ALL POSIBLE
CASES
-------------------------------------------------------------------------

MY PROBLEM IS HOW TO MODEL MY SITATION USING DIMENSIONAL MODEL IN ORDER
TO
GET MODEL THAT WOULD BE BEST FOR DATA WAREHOUSING AND OLAP ANALYSIS.

EVERY DAY I NEED STATISTICAL INFORMATION LIKE:

1. total number of coins owned by mike on some specific day
2. total number of coins owned by mike i some time interval
3. total number of coins that mike lost in some period of time
4. total number of coins that mike obtained in some period of time

ALSO I NEED STATISTICAL INFORMATION OF TYPE:
5. How many coins are owned by coin owners from JAPAN in some period of
time

Time is made of durations, so you need to model peridos of ownership.
I also assume that a coin is unique and has only one owner. Try
something like:

CREATE TABLE Ownership
(owner_id INTEGER NOT NULL
REFERENCES Owners(owner_id)
coin_id INTEGER NOT NULL
REFERENCES Coins(coin_id)
start_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
end_date TIMESTAMP, -- null means current
CHECK (start_date < end_date),
PRIMARY KEY (coin_id, start_date));

Now use " my_date BETWEEN start_date AND COALESCE (end_date,