Suggestions for making SQL relationship setup easier to understand

[mjashanks]

I find the SQL Relationship screen to be a little confusing. I'm never quite sure

1. The direction of the relationship, when I select "One". I.e. What is the "From" table and what is the "To" table
2. The column names section is not obvious. The text gives the impression that new columns are created on the SQL table - which is not the case. The columns are alias columns in the select statement.

I suggest.

1. The table selection should come first. Select your "From" and "To" tables - as is, but before the relationship type
2. The Relationship type should then have options "One customer has many deals" or "Many customers has many deals" (just like the internal tables). In fact, we should have radio buttons, like internal tables
3. Columns Names section - I'm not sure how what to do here. Maybe the field labels should read "Name of Deals, in customer table" and "Name of Customers, in deals table". With pretext - "when the query returns, this is the alias given to the relationship". I don't think we should mention "New column in the table", as it gives the wrong impression.

Triggered by this: #2137 (reply in thread), but i also felt this confusion.

[joebudi]

I briefly thought about this recently.

I think we should simplify the labels:

• From
• To
• Foreign key

I also think the columns names section should sit under an 'advanced' dropdown, with default values provided (user can override). The wording needs revised - agree with your points.

[KernOil]

I agree the SQL Relationship setup took some trial and error to understand what should go where.