Thanks for the replies. To reiterate, every project will have, and only have, a principal, manager, snd supervisor who are employees.
Judging from your suggestions and my trial and error testing, It looks to me that respecting normal form, no matter how you create the structure and relationships you will not be able to automatically access Employee data directly in an output list, input form, or report in 4D. Code will be required to query the Employee table in order to properly display or report on the Projects table with data from the Employee table. If, as is the case in my situation, one has a unique field in the Employee table that is user recognizable such as an Employee Number it could be used in the Project table. No relationships are required. Those fields in the project table can be listed or reported directly. Any additional Employee data can easily be obtained with code, just as one would with relationships, without the use of any relationships or linking tables. That being said, while not providing access to the employee data directly, a many to many linking table would be useful for collecting employees related to projects and vice versa. > On Nov 8, 2017, at 10:06 AM, John Baughman via 4D_Tech <[email protected]> > wrote: > > 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] > ********************************************************************** 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] **********************************************************************
