How to Represent Multiple-Role Tables


I have a set of related tables that I’m modeling in the ER Diagrammer. I would subsequently like to use this model as a source for the Query Builder. There are many instances of tables where a particular physical table will play multiple roles in the logical layer/application.

For example, suppose there are two physical tables, an employee table and a service request table. The service request(SR) table has the PK of the employee in two different columns, one as the employee(FK to employee table) to whom the SR is assigned and a second as the manager of the employee (another FK to employee table) to whom the SR is assigned.

The way I’m currently doing this is to create a ER diagram with either none or only one of the relationship defined and then creating alias tables in the Query Builder. In this case, my model in the Query Builder would have two aliases of employee, one for the employee and a second for the manager, each joining to the appropriate FK in the SR table.

Ideally, I would like to be able to specify all joins in the ER Diagrammer. However, when I do this and subsequently import the tables to the Query Builder, the resultant query contains both joins. I suppose this is strictly correct since the ER Diagrammer is a physical ERD. However, I’d like to have some way to also feed the Query Builder.

Is there a better way to do this? How do other folks handle physical and logical models in Toad for Data Analysts?

Alternatively, I suppose I could build a parallel ‘logical model’ in the Query Builder. If I do this, is there a way to visually build a query with only the tables needed to satisfy the query? Eg, the queries that are built seem to pull in all tables in the Query Builder model. In most cases, they all aren’t needed.

Steve C

I see what you are hoping to do but the ER Diagrammer is not cut out for this. You could set the dependancy level to 0 and this would stop the related tables being brought in. You can make your own joins at that point but it will not accommodate aliased tables.

I would use the Query Builder as you mention at the bottom of this post. Drag in the tables you want and do not select any columns. Add / remove joins as you want and save this tsm file as a template for future queries. The Query Builder is better equip to render logical and physical relationships in its Diagram tab.