Re: Database design - Please comment my thoughts

Originally posted by Vincent
> 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

Hey Vince,

I am a new user with dbforums.. Currently, I am also in the middle designing conformed dimension to cater geographical needs. There are various sources of this data on the web. After analyzing various geo structures across the globe. I am planning to go with 5 levels. As this dimension has to cater various global needs, my suggestion would be to have fixed hierarchy and all fact tables should have lowest granular entries rather than at various levels. Please let me know if this helps..

Thanks,
Srinu

--
Posted via http://dbforums.com