Dimensional modeling problem: need advice from expirienced engeners

SQL Apprentice Question
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



Celko Answers
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.