Foreign key reference
2 posters
Page 1 of 1
Foreign key reference
We have a Location dimension which is at store granularity. Stores can roll up to districts and then regions. The business key is the Location code and we use a surrogate key as the primary key.
We have a new dimension called DimCustomerOwner. An owner can be either a location or a person. Originally it was thought that if the owner were a location, it would only be at store level, so we could reference the primary key from DimLocation. However, a district or a region can be an owner. My question is, do I create a hierarchy helper table in order for district and region to have a primary key? Or is it okay to use the attribute fields, District_Code or Region_Code, on the DimCustomerOwner table? (We have a field called Owner_Type where we can distinguish between Location, District, or Region since for example, '1' can be a store, district, or region.)
Thanks!
Store | District | Region |
1 | 1 | 1 |
2 | 1 | 1 |
3 | 2 | 2 |
4 | 3 | 3 |
We have a new dimension called DimCustomerOwner. An owner can be either a location or a person. Originally it was thought that if the owner were a location, it would only be at store level, so we could reference the primary key from DimLocation. However, a district or a region can be an owner. My question is, do I create a hierarchy helper table in order for district and region to have a primary key? Or is it okay to use the attribute fields, District_Code or Region_Code, on the DimCustomerOwner table? (We have a field called Owner_Type where we can distinguish between Location, District, or Region since for example, '1' can be a store, district, or region.)
Thanks!
hasooyeh- Posts : 5
Join date : 2009-02-25
Re: Foreign key reference
If a person was not involved, I would recommend the hierarchy bridge approach and build out an 'org unit' dimension that includes location, district and store as individual entries.
Trying to do it based on attributes is cumbersome and inflexible.
But how does 'person' fit in? Are these specific individuals, possibly outside the company, or are they positions relating to an org unit?
Trying to do it based on attributes is cumbersome and inflexible.
But how does 'person' fit in? Are these specific individuals, possibly outside the company, or are they positions relating to an org unit?
RE: Foreign key reference
Thanks for the reply!
We will actually have two fields, one to reference a location and one to reference a person. Regarding the person, it will be somebody within the company.
Essentially, one field on DimCustomerOwner, either the Location or Person, will be blank for each record because a customer must have one or the other, not both.
So my hierarchy helper table would contain the fields below, correct?
Surrogatekey, Location_Code, Location_Type
We will actually have two fields, one to reference a location and one to reference a person. Regarding the person, it will be somebody within the company.
Essentially, one field on DimCustomerOwner, either the Location or Person, will be blank for each record because a customer must have one or the other, not both.
So my hierarchy helper table would contain the fields below, correct?
Surrogatekey, Location_Code, Location_Type
hasooyeh- Posts : 5
Join date : 2009-02-25
Similar topics
» Foreign key referential integrity in the fact tables
» Null Foreign Key
» Foreign Key Constraints in Data Mart Design
» Fact Table Foreign Key reference question
» Foreign key referential integrity in the fact tables (Oracle 11g)
» Null Foreign Key
» Foreign Key Constraints in Data Mart Design
» Fact Table Foreign Key reference question
» Foreign key referential integrity in the fact tables (Oracle 11g)
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|