Table structure

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.