It looks like you only have one primary key here ( _KP_Serial ID). I suggest you use this rather than the employee number to make your self-join relationship, just in case the employee numbering system changes at some point.
It looks like you have two foreign keys ready to assign a Junior and a Senior Manager to each employee (Junior Manager Employee No and Senior Manager Employee No). It might be better long term to name these starting with a _KF_... since they will be used for foreign keys.
In this case however, it looks like it might suit better to have a table of "Departments", so that if a department senior or junior manager changes, you only need to change it in one place, rather than going to each employee's record and changing their managers.
That table would contain an ID, the name of the department, who the senior and junior managers are, and any other relevant fields.
Then you only need to select the department for the employee, and that can automatically show who their Junior and Senior Managers are.
Hope that gets you started?
You can add as many table occurences you want, pointing to the same table, but using different relationships. So you can "self-join" based on junior manager or senior managers like so (one way to do it ) : (there is only table in this database)
You can have as many "primary" keys as you want, but I think you want one primary key (id = id of the employee) and 2 foreign keys id_junior_manager and id_senior_manager (the value of these the primary key (id) of their own record.) In the example screenshot the id_employee is actually not needed.
I used to add _fk to the end foreign keys.
I would go with a hierarchiical table model where you have _pk_employee_ID , and _fk_manager_ID only.
It solves this domain problem and allows for infinite levels in "managerial" structure.
The top level employees would be the only employees with NO value in __fk_manager_id
I would suggest you not use an underscore as the first character of a field name, since this creates difficulties when using ExecuteSQL and with external ODBC integration. Even if you think you won't need to use these, my recommendation is, Murphy happens.
Thanks for you quick reply. I will give that a try. All makes sense when someone else looks at it...
I come from Visual Foxpro where everything is SQL based so this will save me a lot of time.. (when I get my head around it ) ..
This looks like what I need. I will keep the employee ID though as it may change in the future. Just need to get clear in my head how to create all the initial links (foreign keys) after importing the file.
Thanks again. I will need some sort of Hierarchical database and have achieved it in Visual Fox pro.
The database was like a family tree and had to maintain nodes, left, right and current.
I would be interested to know if this if has been solved already and how to go about it. In Visual Foxpro its all calculated by building arrays and recording every record id as you work back up the chain. In Filemaker I'm not sure which way to go.