1

I'm designing and building a sales fact table in a star schema and I can't seem to work out how to go about the following problem:

A customer can have 1 or 2 accounts, but an account can only belong to 1 customer. So this is a 1 to many relationship.

Should I create dimensions for customer and account, and link them with a bridge table?

In the final fact table I would have as example rows:

| date_id | cust_id | Acc_id | count(sales) |  
|    1    |    150  |   25   |      1       |  
|    1    |    150  |   26   |      1       |
Jo Douglass
  • 2,055
  • 1
  • 19
  • 30
Grant McKinnon
  • 445
  • 3
  • 7
  • 17
  • Is ACC_ID unique in its own right? It's not a compound key with CUST_ID or anything daft like that, – APC Mar 25 '15 at 05:07
  • Is it possible, to have separate dimensions for Account and Customer, and have a foreign key in the account dimension linking back to the customer dimension – Grant McKinnon Mar 25 '15 at 05:15

2 Answers2

2

Simply make the Account and Customer Dimensions. Do not link them with a foreign key - that's how you'd do things if you were creating a fully normalized schema, rather than a Star schema. The link between Customer and Account is held in the Fact table(s) - because you have a row of data holding Cust_Id 150 alongside Acc_Id 25 and another row of data holding the same Cust_Id against Acc_Id 26, it will be apparent in any OLAP layer you build over it that these are related.

Note that you could also simply have an Account Dimension, and hold the Customer's details as attributes on the Account. Without knowing the rest of your model it's impossible to tell whether this is a more suitable solution, though.

Jo Douglass
  • 2,055
  • 1
  • 19
  • 30
  • your first suggesting makes a lot of sense. thanks! I would rather seperate them so I'm not duplicating Customer details. Also there are some account specific details that could be stored in a account dimension. Thanks for clarifying that I do not need to link them. – Grant McKinnon Apr 24 '15 at 01:30
  • @GrantMcKinnon Glad it's helped! Keep in mind that you shouldn't worry about data duplication (as long as it's intentional data duplication like the type we're discussing) in star schemas. Both the Facts and Dimensions should be decided by the grain - so if you have a Fact table that has Customer as its grain then you need a Customer Dimension. If not, there's no real gain to having a separate Dimension. Takes a while to get used to that mindset, I find! – Jo Douglass Apr 24 '15 at 07:01
0

Create a Customer table, with Cust_ID as the Primary Key. Create an Account table with Acct_ID as the Primary Key and Cust_ID as a foreign key. Each account must have one and only one customer, but a customer can be listed on more than one account.

What is a "sales fact table"? Are you trying to track orders?

Kevin L
  • 1
  • It is a fact table from a star schema in a data warehouse. I think you are referring to a relational database, This data is already stored in relational tables. I'm consolidating it into one schema with a total sales as the aggregate function and customer and account as dimensions. Each row of the fact table corresponds to one sale to a account, that has a customer. A sale can be only be to one account and customer but what Im stuck with, is how to link customer and account dimensions – Grant McKinnon Mar 25 '15 at 04:16