Data Model Optimization

[MarcER86]

Good afternoon,

I would like to know how would be more optimized data model for next scenario:

Customer Table: CustomerId, CustomerName, CustomerCategory

Invoices Table: CompanyId, CustomerId, CountryId, DocumentNumber, DocumentType, PostingDate, Entry, EntryType, Amount

Payments Table: CompanyId, CustomerId, CountryId, DocumentNumber, DocumentType, PostingDate, Entry, EntryType, Amount, InvoiceNumber, InvoiceEntry

Each CustomerId is related to a unique CountryId, so I have created a CustomerDim table including CountryId, and I've removed it from Invoices and Payments.

Payments['InvoiceEntry' ] is linked to to one Invoces['Entry']. 

At the beggining my first data model created was:

CustomersDim (1->N) Invoices

Invoices (1->N) Payments

Is it correct? In this case I would have 1 dimension table and 2 fact tables? To get a Star Schema should I have to create a "Transactions" table with invoices and paymentes, and then some Dimension table linked to it?

I know there are a lot of questions, but I am new at this and I want to do it as better as possible.

Thanks,

Er

[MarcER86]

My initial data model links the dimension table to invoices fact table, and then invoices fact table to payments fact table. Therefore there is a connection between two fact tables, which doesn't follow star schema if I'm not wrong. Am I right?

[v-jianboli-msft]

Hi @MarcER86 ,

Based on the information you provided, it seems like your initial data model is correct. You have one dimension table (CustomersDim) and two fact tables (Invoices and Payments).

To answer your question about creating a "Transactions" table, it depends on your specific business needs. If you need to analyze transactions as a whole, then creating a Transactions table that combines Invoices and Payments could be useful. However, if you primarily need to analyze Invoices and Payments separately, then keeping them as separate fact tables would be appropriate.

In general, the goal of a star schema is to have dimension tables that provide context for the facts in the fact tables. So, if you have additional dimensions that provide context for Invoices and Payments (such as Product or Salesperson), you could create separate dimension tables for those and link them to the appropriate fact tables.

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.