Advice of Table Structure

[shane_spencer]

I've been given an App that I need to do some more development and I'm wanting some advice on how to structure the table(s) to get the data in a format that best suits my purposes. Presently there are two tables - Goods Dispatched and Invoices for these.

As you can see there's a synthetic key which I'm not happy about BUT it does the job. i.e the Company / Country / Year / Month are from BOTH tables, the VAT is from INVOICES and the Intrastat is from GOODS. By selecting from the list boxes the data in the table will show the comparison for specific Months, Company, Country etc. There's even another chart that will show a more detailed breakdown by Invoice number.

If I break the synthetic join the table does not work - even if I use set analysis to equate Goods Month to Invoice Month etc. (Though set analysis works well if I create separate charts with the dimensions specific to the expression).

I was thinking of creating one Table of data instead. But what would be best? just create the missing fields - these are more dimensions such as customer name and tax rate etc - in each data set so they concatenate? or join the Tables using the LINK field (which is essentially an Invoice number that exists in both)? The tricky bit is that the Month/Year in GOODS may be different to the Month/Year in INVOICES for each order as they are not dispatched and billed at exactly the same time so I'm unsure how that would affect the decision. 

Really after some advice please.

[shane_spencer]

This created a 3 way link - not sure if this is ok or not.

[Kushal_Chawda]

3 way link is ok if Link is created on proper Key fields

[shane_spencer]

Ok Thanks. I was thinking of having iLINK in Invoices, gLINK is Goods, and iLINK, gLINK and LINK in the Link Table - that way there would not be a 3-way link. Would that be better? Or would that not work?

[Kushal_Chawda · Accepted solution]

That all depends on the granularity on which your fact exists. If your invoices are exists till month level and Goods just at Year level then you can create iLINK  & gLINK as both the Fact are available at different level, but if your Invoice and Goods Fact are at same level then no need to create the separate link you can go with 3 way link approach

[shane_spencer]

Thanks everyone for some great advice. In the end I decided that the LinkTable worked best for the data I had: