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.