Connecting Companies table to People table

[Motlaq_AlMutair]

Hi,

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?

• Motlaq

[ScottWorld]

Your best solution would be to create a base with a many-to-many relationship structure.

Here is Airtable’s support article on that:

Airtable Support

Airtable's guide to many-to-many relationships

What sets Airtable apart from ordinary spreadsheet applications is its ability to link related concepts together. Knowing how to represent the relationships between different groups of objects can ...

[Motlaq_AlMutair]

Thank you for your answer, Scott. And I’d like to take this opportunity to thank for your contributions in this community. Whenever I face an issue I come here knowing that someone must have had it, only to find you helping. Thanks!

To our issue at hand :grinning_face_with_sweat: ، many-to-many relationships here helps us connecting companies to employees but it doesn’t help when it comes to job titles which is the main issue. One person might be connecting to two companies in two different ways and that’s why I’m struggling with; Telling Airtable that person A is a ‘Founder’ in Company X and a ‘Board Member’ in company Y.

The two approaches I’m thinking of are:

• Having duplicate records of the same person with each record having a company and a job title. This doesn’t seem to be a good solution as the idea is to link the person themselves to the companies. Treating them as two people doesn’t make sense.
• Creating a job title table that is linked to the company and the employees. This means that employees table isn’t directly connected to the companies table but through the job titles table. So person is connected to company X through the ‘founder’ relationship/status AND connected to company Y through the ‘board member’ relationship/status.

I’m still trying to wrap my head + experimenting with the second approach to see if it’s the way to go.

Curious what you think though…

[Kamille_Parks]

The second approach is probably exactly what Scott was suggesting.

[ScottWorld]

Motlaq_AlMutairi:

Thank you for your answer, Scott. And I’d like to take this opportunity to thank for your contributions in this community. Whenever I face an issue I come here knowing that someone must have had it, only to find you helping. Thanks!

That is very kind of you to say! :hugs: Thank you so much! :smiling_face_with_three_hearts:

Kamille_Parks:

The second approach is probably exactly what Scott was suggesting.

Kamille is 100% correct. :100:

Your 2nd approach is called a many-to-many relationship, and it’s what I was suggesting.