5 Replies Latest reply: Apr 15, 2011 10:38 AM by Oleg Troyansky RSS

    Reference key problem

      error loading image


      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?


        • Reference key problem
          Miguel Angel Baeyens de Arce

          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.

            • Reference key problem

              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.


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


                • Reference key problem


                  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.

                  • Reference key problem
                    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.

                • Reference key problem
                  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...