Database design - Please comment my thoughts
From: vincent <nospam_vteyssier_at_nerim.net>
Date: Mon, 26 May 2003 20:29:48 +0200
Message-ID: <pan.2003.05.26.18.29.46.123813_at_nerim.net>
Hello,
I'm studying a banking activity before building a datamart. I have one fact table (trading transactions) linked to a hierarchy dimension (geography). This dimension is 6 table deep, the 1st is linked to the fact table. The 1st level corresponds to the most atomic items and the 6th, to the most global (groups of countries) Until now, no problem but :
- the fact table contains one transaction type which needs to be linked
to the 1st level (most atomic) of the dimension : ok no pb, done.
- the fact table also needs to be linked to the 4th level of the dimension
coz one transaction type only uses the geographic dimension at the 4th level (most global).
Of course, I have foreing keys on the fact table Do you think this design schema is really good ? I'm afraid of this kind of "short circuit" (or loop) between my hierarchy dimension and I'm afraid of ETL process ...
Date: Mon, 26 May 2003 20:29:48 +0200
Message-ID: <pan.2003.05.26.18.29.46.123813_at_nerim.net>
Hello,
I'm studying a banking activity before building a datamart. I have one fact table (trading transactions) linked to a hierarchy dimension (geography). This dimension is 6 table deep, the 1st is linked to the fact table. The 1st level corresponds to the most atomic items and the 6th, to the most global (groups of countries) Until now, no problem but :
- the fact table contains one transaction type which needs to be linked
to the 1st level (most atomic) of the dimension : ok no pb, done.
- the fact table also needs to be linked to the 4th level of the dimension
coz one transaction type only uses the geographic dimension at the 4th level (most global).
Of course, I have foreing keys on the fact table Do you think this design schema is really good ? I'm afraid of this kind of "short circuit" (or loop) between my hierarchy dimension and I'm afraid of ETL process ...
Last thing : this geographic hierarchy dimension needs to be used both for "destination" and "origin". Should I use foreing keys and have two links to each hierarchy dimension tables or duplicate the hierarchy (view ...) ?
Sounds a bit messy, I agree.
Thank you for your comments.
Vince
Received on Mon May 26 2003 - 20:29:48 CEST