Table structure

%3CLINGO-SUB%20id%3D%22lingo-sub-2832463%22%20slang%3D%22en-US%22%3ETable%20structure%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2832463%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELooking%20for%20some%20advice%20please%20on%20how%20best%20to%20structure%20tables.%20I%20have%20a%20'Person'%20table%20which%20contains%20the%20name%20and%20contact%20details%20of%20various%20persons%20associated%20with%20our%20organisation.%20Some%20of%20the%20people%20in%20the%20table%20are%20Employees%2C%20being%20a%20subcategory%20of%20Persons.%20For%20the%20employees%2C%20I%20want%20to%20be%20able%20to%20record%20their%20name%20and%20contact%20details%20(as%20happens%20in%20the%20Person%20table)%20but%20I%20also%20want%20to%20be%20able%20to%20record%20additional%20details%20about%20employees%20only%2C%20such%20as%20DateOfBirth%2C%20StartDate%2C%20etc.%20Is%20it%20best%20to%20have%20the%20Employees%20table%20as%20a%20separate%20table%20linked%20to%20the%20Person%20table%20somehow%20or%20should%20I%20just%20have%20additional%20columns%20in%20the%20Person%20table%20to%20capture%20the%20extra%20information%20for%20employees%3F%20Thanks%20in%20advance.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2832463%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAccess%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2832877%22%20slang%3D%22en-US%22%3ERe%3A%20Table%20structure%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2832877%22%20slang%3D%22en-US%22%3EThe%20best%20approach%20may%20depend%20on%20how%20many%20additional%20fields%20you%20would%20need%20for%20employees%20and%20the%20relative%20number%20of%20employees%20compared%20to%20other%20people%20(clients%2Fcustomers%3F).%3CBR%20%2F%3EAlso%20some%20people%20could%20be%20both%20employees%20and%20customers.%3CBR%20%2F%3EWill%20you%20need%20additional%20fields%20for%20those%20who%20aren't%20employees%3F%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20you%20decide%20on%20a%20separate%20table%20for%20the%20additional%20fields%20you%20can%20link%20it%20to%20the%20primary%20key%20field%20in%20your%20persons%20table%3C%2FLINGO-BODY%3E
New Contributor

Hi

 

Looking for some advice please on how best to structure tables. I have a 'Person' table which contains the name and contact details of various persons associated with our organisation. Some of the people in the table are Employees, being a subcategory of Persons. For the employees, I want to be able to record their name and contact details (as happens in the Person table) but I also want to be able to record additional details about employees only, such as DateOfBirth, StartDate, etc. Is it best to have the Employees table as a separate table linked to the Person table somehow or should I just have additional columns in the Person table to capture the extra information for employees? Thanks in advance.

3 Replies
The best approach may depend on how many additional fields you would need for employees and the relative number of employees compared to other people (clients/customers?).
Also some people could be both employees and customers.
Will you need additional fields for those who aren't employees?

If you decide on a separate table for the additional fields you can link it to the primary key field in your persons table

@deansmith19 

While there is some room for discussion about the most appropriate way to handle situations like this, the more traditional approach would be a sub-table of those details pertinent to the sub-class, in this case, employees and potentially customers. It is harder to handle gracefully in an interface, though, so sometimes a compromise is okay.

Thanks for your replies. I found the following information soon after I posted which seemed to describe my situation exactly: https://support.microsoft.com/en-us/office/video-create-one-to-one-relationships-a5868c76-50ea-44b7-.... I have set up a one-to-one relationship between the Persons and Employees table and added the additional details needed in the Employees table.

Thanks again for your assistance.