I’m having an issue with linking two tables together. I have a ‘Companies’ table and an ‘Employees’ table. In the employees table I have a column named ‘Job Title’ in addition to the column linked to the companies table. My issue is that sometimes a person is connected to two companies. One can be an executive in a one company and a board member in another. So, how do you state or clarify the two job titles in the database schema. You can easily have two companies in the “companies” column but the Job title will be wrong as it might not specify the company where he/she hold that job title (Imaging the companies column having two companies and the job title saying ‘Executive’ or ‘executive, board member’ to make it more ambiguous!)
I thought of having a column in the companies table named “board members” and link it to employees table and then another column named executive and link it to employees table too. But I think it’s a bit limiting in that you can have a column for every title since different companies might have different structures/hierarchies. Any other solutions?