John, The automatic relations in 4D are really handy for output layouts and selection to array, but unfortunately are not much help in your database model. What I have done in the past is create as many Clone Tables as needed, like Employee_1 and Employee_2 that only contain the primary key ID and NAME. These tables are maintained/kept in sync by a trigger in the Employees table. Then use automatic relations from the ProjectMangeEmployeeID and SupervisorEmployeeID to the clone tables.
Hope this helps. Bart > On Nov 8, 2017, at 2:00 PM, [email protected] wrote: > > Date: Wed, 8 Nov 2017 10:06:22 -1000 > From: John Baughman <[email protected]> > To: Chip Scheide via 4D_Tech <[email protected]> > Subject: Normalization question > Message-ID: <[email protected]> > Content-Type: text/plain; charset=utf-8 > > Given a Project table and an Employee table where Employees can be Project > Principles, Project Managers, and Project Supervisors, what would be the > proper way to create a normalized table structure in 4D. In the end an > assigned employee’s information, like name for example, would automatically > be displayed in the project’s output form for each of these assignments. > > I am pretty sure using the proper Select/Join commands in SQL this could > easily be done, but I am falling short with 4D as none of these structures > work... > > [Employees]ID <——— [Projects]PrincipleEmployeeID > <——— [Projects]ProjectMangeEmplyeerID > <——— [Projects]SupervisorEmployeeID > > OR using linked tables…. > (There is only one record in each linking table for > each employee assigned a role) > [Employees]ID > <———[Principles]EmployeeID<———-----------[Projects]PrincipleEmployeeID > <———[ProjectManagers]EmployeeID <——— > [Projects]ProjectMangeEmplyeerID > <———[Supervisors]EmployeeID <——— > -------[Projects]SupervisorEmployeeID > > The only way I can see to make it work is to denormalize it and put the > pertinent data from the Employee table into the linking tables in which case > the relationship between the employee table and the linking tables become > irrelevant. > > I have no problem denormalizing, but thought this might be a good exercise to > retain a few more cells in my aging brain. ;-) > > John > > > John Baughman > Kailua, Hawaii > (808) 262-0328 > [email protected] ********************************************************************** 4D Internet Users Group (4D iNUG) FAQ: http://lists.4d.com/faqnug.html Archive: http://lists.4d.com/archives.html Options: http://lists.4d.com/mailman/options/4d_tech Unsub: mailto:[email protected] **********************************************************************
