CODEWITHSUNDEEP
oracle-databasedata-warehousedimensional-modelingstar-schema
Grant McKinnon
Grant McKinnon

Reputation: 455

Dimensional data warehouse customer with multiple accounts

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       |

Upvotes: 1

Views: 1224

Answers (2)

Jo Douglass
Jo Douglass

Reputation: 2085

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.

Upvotes: 2

Kevin L
Kevin L

Reputation: 1

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?

Upvotes: -1

Related Questions