I am drawing out how my database will store and access data and I've hit a little snag. I have a table for Company and another for Persons. Under most usual cases the Company table will link to one person and A person may link to one company. I'm trying to figure out the best way to link these two tables together as once Company can have different people with different roles (ie. Desicion-maker, ceo, marketing rep) and one person may own, or have a role in multiple companies.
I don't think simply having a field with company_id in the person table would do much help the moment that person has multiple roles in one or more companies, as well as I don't think it is good to ...
A solution came to me as I was asking
My company table has the following fields
ID, Company name, and then id links to address date, phone, and person
My Person tables has
first/last name, title/job position, phone, address, ....
My solution is to make the title/job a separate table that holds the person id and the company id and the role.
What are your impressions on this solution?