Forum Discussion
Table structure
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
- 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-892d-43f2af2802e0. 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. 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.
- 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
