Reference key problem

[#1]

Hi All,

guys.. what's the better way to resolve this problem:

In this model, the table TabA join TabB only using the field tabBID. And each table be join with TabC using tabCID each one.

To solve this, I'm thinking to create other tabC, with

other key (tabCID2). And use this new key to join with one of each other tables. Am I on right way?

tks

[Miguel_Angel_Baeyens · Accepted solution]

Hello Rodrigo,

If you want to avoid the synthetic key, you can create composite keys, for example

TabA:LOAD AutoNumber(tabCID & '/' & tabBID) AS LinkAB, tabAID, Afield1FROM tableA; TabB:LOAD AutoNumber(tabCID & '/' & tabBID) AS LinkAB, tabBID, Bfield1FROM tableB; TabC:LOAD tabCID, Cfield1, Cfield2, Cfield3FROM tableC;FROM table;

Anyway, a synthetic key is not an error by itself, it only means that you have more than one table with more than one field named alike.

Hope that helps.

[Oleg_Troyansky]

This structure needs to be simplified. The specific way would depend a lot on your business needs and on the relations between those tables.

As an example, if Table A and Table B can be combined into a single table (don't worry about de-normalization, we do it successfully every day), then you are only left with two tables joined by a single key, which is fine.

In a different situation, the right solution would be concatenating all three tables into a single fact. Depending on your analysis needs, this may or may not be the right solution...

[#4]

Hello, Miguel!

I understood your diea, but the problem is that tables TabB and TabC are linked only using tabBID, and not a composite key like tabBID+tabCID.

Oleg,

If I use "join" and "concatenate" commands, would I lost data? Or can I solve this using LEFT, RIGHT and FULL joins?

tks

[#5]

Rodrigo;

I came across your thread looking for composite key help, but I can answer your question to Oleg -

You won't lose data. Without knowledge of the contents of your tables I can't gauruntee it but we used to have a similar problem to what you are having and solved it by concatenating two tables and left joining another. Works a treat and having all the data in one table improved performance.

[Oleg_Troyansky]

You will never lose data using COncatenate (it's appending one table to another, similar to SQL term "UNION ALL", only allows different table structure).

With LEFT, RIGHT or INNER join, you may lose some data that doesn't exist in the the "primary" table (the type of the join determines what table is "Primary"). By default, JOIN is outer, so you won't lose any data if you use the default.