Database structure advice

[CloudMonkey]

Hi,

I have a database with 3 tables:

Actuals (Volumes)

Actuals (Money)

Budget (Volumes)

Each of those tables has other tables linking into them (branch/region/area mappings, fiscal period mappings, product description mappings and public holiday mappings)

I'd like to create a single table with the acutals and budget (with mappings coming off it) to make the database later on (i.e. in one column I can define whether a row is actuals/budget and in another column I can define wither a row relates to volume or money)

I tried appending the 3 main tables together and keeping the orginal mappings but the approach failed, because table links require at least one table's values to be unique. (for example actual data has product codes but the budget does not, resulting in many NULL values in the product code field - then when I try to link the product descriptions table, there is a failure).

Please can you tell me how the best approach for structuring this database? Is it to merge the main tables rather than append them?

Thanks,

CM

[tringuyenminh92 · Accepted solution]

Hi @CloudMonkey,

You need to have some master/dim tables like products, time cause your 3 tables are transaction tables, so you could choose new table and values() or distinct() function to achieve this.  And you could refer topic Budget Patterns of Marco Russo.

(Sales and Budget tables in topic are your transaction tables like Actuals, Budgets)

[v-huizhn-msft]

Hi @CloudMonkey,

Have you resolved your issue? If you have, please mark the helpful reply as answer, or share your solution, so that more people can find workaround form here. Thanks for understanding.

Thanks.
Angelia

[tringuyenminh92 · Accepted solution]

Hi @CloudMonkey,

You need to have some master/dim tables like products, time cause your 3 tables are transaction tables, so you could choose new table and values() or distinct() function to achieve this.  And you could refer topic Budget Patterns of Marco Russo.

(Sales and Budget tables in topic are your transaction tables like Actuals, Budgets)

[CloudMonkey]

Hi tringuyenminh92,

Thanks - the budget files aren't split by product - the budget files are just split by branch/fiscal period/GL account. Does that make a difference?

CM

[v-huizhn-msft]

Hi @CloudMonkey,

Just as @tringuyenminh92 posted, filter your resource table after some operation. You can use the transaction relationship between two tables, and construct them.

Best Regards,
angelia

[tringuyenminh92 · Accepted solution]

Hi @CloudMonkey,

yes, it's same situation, there is no different