Database structure best practice for accounts department

[CloudMonkey]

Hello,

I work in an accounts department with quite a few sources of data:

Sales records from sales database (weekly cut)

Sales records from sales database (monthly cut for management accounts)

Financial system data (monthly cut for management accounts)

Budget (money)

Budget (sales volumes)

Forecast (money)

Forecast (sales volumes)

These are all held in different tables. Please can you tell me:

1. Whether it's best practice to combine these first into one table, and then map in the other master data (e.g. cost centres, fiscal periods etc).

2. If I combine the tables I think I need to add extra columns (listed below) to categorise the records. Is this best practice or is there a better approach?:

Actual or budget of forecast

Weekly or month-end cut

Sales volume or money

Many thanks for your help,

CM

[CheenuSing]

Hi @CloudMonkey

Can you share some dummy data of each datasource you have mentioned.  Depending on the content of data it will be possible to suggest a workable data model.

One thing that comes to my mind if you individual Sales Records summing up of the same can be done using measures. The monthly cut data you mentioned would then become a visual.

As to whether it should carry volume and money separately. It is adviced to have them in a single table for budgets, forecasts etc.

Ultimately data model depends on the end user requirements.

Do share some mock data of the tables.

Cheers

CheenuSing 

[CahabaData]

I agree with @CheenuSing in that modeling is often interative/reactive to what the goal is for the visuals.  So I would not think you need to immediately / automatically manipulate these tables as the 1st step.

Their description indicates they are aggregate values, not daily or sales transactions.  So when comparing Budget to Weekly Cut or Forecast to Monthly Cut, etc - there needs to be common join field on both sides (in both tables) that is correct along with an understanding of the common granular time intervals used by the differing tables.