Help needed on data modelling

[#1]

I'm stuck with this case:

Table: TRANSACTIONS with link to tables CUSTOMER  and ITEM

As you would expect this holds the sales data

Now I want to add the budgets. The budgets are per CUSTOMER and PRODUCTGROUP.

PRODUCTGROUP is 1 level higher then ITEM.

I've tried:

- ad the budget table and link to ITEM.PRODUCTGROUP and to CUSTOMER.ID

>> this gives a circulair reference (loop)

- concatenating the budget table to the TRANSACTIONS table

>> this gives a synthetic key as the fields PRODUCTGROUP  and ITEM.ID both exist in the ITEM table as in the TRANSACTIONS table

Hope to find an idea here.

Thanks,

Johan.

[#2]

Hi

One idea can be to create a link table and then connect Customers/Items/Budgets with Transactions using this link table.

[kuba_michalik]

One thing that comes to mind - keep the concatenation and add PRODUCTGROUP directly to the fact table (use mapping), then drop it from ITEM table. This would get rid of synthetic key.

[#4]

Thanks for the quick responses.

Meanwhile I've concatenated the the list of PRODUCTGROUP's to the ITEM table as ITEM.ID. Now both the TRANSACTIONS  as the (concatenated) budget lines link to ITEM.ID. That seams to work, just need to test the results.

But Kuba's method is probably better, when my test fails, I'm gonna try that.

I'm not familiar with Linked tables. Maybe I should 🙂

Johan