April 23, 2015 at 10:03 am
Hello folks,
I'm trying to figure on designing a proper E-R relationship with in the database but its just so twisted that no matter what i do, i end with a cartesian product.
Fact tables - has ordernumber and physician. (order number is primary key. Physician is not unique)
Dimension tables:
Physician table - this has the territory manager associated with it and the zipcode.
-- so physicianid is unique and the territory manager isnt.
Zipcode Table
This has all the zipcodes which can map the physician table by zipcode match
County Table
This has area codes and county and cities and the zipcodeid which is the foreign key to zipcode table.
I am ending up with the below cardinality..
Fact (physicianid)-----> Physician (physicianid)
Physician (zipcode) >----- Zipcode (zipcodeid)
Zipcode (zipcodeid) -------< County (zipcodeid)
My end result is to calculate total orders by physician and do a hierarchy analysis based on state, county, city, zip which can also be rolled up from a territory manger level for a high level . :w00t:
Please help..
May 1, 2015 at 7:24 am
In my opinion, it would be best to create a "Geography" dimension which contains Zip Code, County, State, etc.
If the Zip Code attribute of a physician is the only way in which you can associate an order with the geographical attributes, then it can be retained in your "Physician" dimension and used as a reference/lookup when populating the fact table.
You should end up with a star-schema here, with the surrogate keys of both "Geography" and "Physician" dimensions in the fact table. The rest will work itself out through aggregation.
May 1, 2015 at 3:53 pm
Is this kinda what you are thinking? http://www.kimballgroup.com/2012/02/design-tip-142-building-bridges/
May 4, 2015 at 1:58 pm
benjamin.seidle (5/1/2015)
Not really. A bridge table (as described in the above) is useful when you have to represent a many-to-many relationship between a fact and dimension, or between 2 dimensions (which will really be nothing more than a fact-less fact).
If you design the structure as I suggested, the fact table will describe all the necessary relationships between physicians and geography, and no bridge table should be required in my opinion.
If you need to be able to describe the relationship between physician and geography in the absence of a fact record, then you'll have to use a bridge table (or fact-less fact) to represent that relationship.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply