On Wed, 8 Nov 2017 12:30:14 -1000, John Baughman via 4D_Tech wrote: > Thanks for the replies. > > To reiterate, every project will have, and only have, a principal, > manager, and supervisor who are employees. until this is not the case... :)
> 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. using normal form, and starting your displays and reporting from the linking table you should not have to use code (only 4D relations). However... there can/could easily be issues using automatic relations as these can/could/would (depending on the rest of the structure) cascade into a loop changing you current selection of one or more tables unexpectedly. So - I would suggest using code and manually activating the relations, or insuring that other relations in the system are NOT automatic to avoid the below. ex: (all auto relates) [Employee] -> [Emp_proj_Roles] -> [Projects] -> [Invoicing] -> [Invoice_Items] -> [Employee] (for their time) this last relation changes [Employee] records > 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. until the employee number changes, or is re-used, or... who knows what goes through some users minds.. :) > 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. and as I pointed out before - it allows collection of data related to the [Emp_Proj_Role] which is specific to the the employee and/or Project. for example : Joe on Project X has the Role of 'Supervisor', but also has additional responsibilities, or has fewer responsibilities as this is his first project as supervisor, and Fred is his over seer. > >> 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] > ********************************************************************** --------------- Gas is for washing parts Alcohol is for drinkin' Nitromethane is for racing ********************************************************************** 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] **********************************************************************
