John: This is a link <http://www.databaseanswers.org/data_models/index.htm> to a treasure trove of data models. I checked a couple of the models in the "employees" section and the generic model <http://www.databaseanswers.org/data_models/employee_generic_model/index.htm> might provide some ideas.
Reading through your table names, you're defining Principles, PM's, and Supervisors as separate entities. That may be justifiable but my tendency would be to see those as one or many "roles" for one or many organizations, each role having a start date, end date, etc. The generic model has "assignments" but doesn't support the concept of "role". Hmm. A table defining a role would be a recursively linked table to define relationship(s) between employees. "Data Model Patterns" by David Hay could be a good source of insight. It's not an easy read but the author reviews numerous enterprise data models in detail. -- Douglas von Roeder 949-336-2902 On Wed, Nov 8, 2017 at 12:06 PM, 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] > ********************************************************************** ********************************************************************** 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] **********************************************************************
