Hi everybody,

first of all let me tell you that I'm VERY new in this field.
Let me describe the problem for which I'need your advice.


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 coins are added
- 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



PLEASE ADVICE ME HOW TO DESIGN MY MODEL IN ORDER TO OBTAIN BEST
POSSIBLE PERFORMANCE
BECAUSE NUMBER OF OBJECTS IS REALY HUGE, AND SITUATIONS FROM ONE DAY TO
ANOTHER CAN
BE DRASTICLY CHANGED.


I COUNT ON YOU
Milicic Marko

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)
ON UPDATE CASCADE,
coin_id INTEGER NOT NULL
REFERENCES Coins(coin_id)
ON UPDATE CASCADE,
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,
CURRENT_TIMESTAMP) " in your queries.