Create link table between 5 tables

[sifatnabil]

Hi,

I have 5 tables that have a common date. I have created a link table to create a star schema to join the dates, but while this is done, I need to associate a common ID between 2 tables. How can I include that in the star schema? The ID is only common between 2 tables out of 5.

The star schema looks like this:

Thanks.

[#2]

Instead of * provide individual columns.

Auto number creates a consistent key based on the arguments.

Sent from my iPhone

[#3]

Actually we are trying to avoid the synthetic key problem here.

So we combine Key and Trade id to form a new surrogate key. Once we form it we delete the key and trade id columns from the respective tables, so they exist only in the mapping table.

Duplicate error is because we selected key, traded and *. Give individual column names and that will be fine.

[sifatnabil]

Thanks Ram - this works if I concatenate the 3rd table in MappingTable, but when I try to concatenate the 4th table (AnnulmentReport), the PricingReport disassociates like below (note that PricingReport has only "Date" as common field). How can I concatenate the 3 extra tables in the MappingTable?

[#5]

if the PriceReport has got only Date why are you bringing that into the Mapping Table. I suggested the Mapping Table just for the 2 Tables that you mentioned initially that shared Key and DerviedTrade_id. If you have the 3rd table that has the same 2 columns, you can bring that in using the same mechanism. But don't bring the PriceReport into this mapping table. It can exist on its own.

Thanks.

[sifatnabil]

I see what you mean. But if I leave the PricingReport on its own, its Date field won't have any association to the TradeReport and the GUIBOSReport. Essentially, I would like to attach 3 more tables like this that share only the Date field to the TradeReport and GUIBOSReport (which are already linked above). How could I do that?

The tables are like this:

Table1 - Date, TradeID

Table2 - Date, TradeID

Table3 - Date

Table 4 - Date

Table 5 - Date

[#7]

Ok. Have one table for Date alone

Have one mapping table as follows:  Date, TradeID, SurrogateKey

Now remove Date and TradeID from Table 1 and Table 2.

Now it will be like this.

Table1 and Table 2 are joined using mapping table. Mapping table is joined to the Date table using the Date. And Table 3, Table 4 and Table 5 are joined using the Date table.

Which means all the 5 tables are linked now.

Does that help?    

[#8 · Accepted solution]

Attached the script and the resulting data model.

Hope that helps.

[#9]

Hi Sifat,

Hope so it will help..

Do the following

Normal table

1-Key

2-Key

3-Key

4-Key+DerivedTrade as D_Key

5-Key+DerivedTrade as D_Key

Link_tbl:

1-Key

   D_key

2-Key

   D_key

3-Key

   D_key

4-Key

5-Key

> Make use of Null() in place of field which is not available while creating Key in Link_table 4 & 5 .

Thanks & Regards,

Harshal

[sifatnabil]

Sorry for the late reply Ram, but thanks this worked. I believe I understand joins and concats better now, cheers!

[#11]

You are most welcome!

Cheers.