Two tables that can infinitely link each other

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?

ahhhhhh, light bulb, Now I get you

To many different things are going on in the same tables. I would be storing attributes that belong to different entities in the same table and that is what would complicate things. I’m on the same page now. I’m going to keep it simple for now and extend only if a need for it becomes apparent, instead of trying to account for what could happen in the future. Thank you very much, I don’t know why but I find a certain joy just thinking about how to organize database structures :slight_smile:

ahh, I see I misread your original post; Ok I will go back to the drawing board to see what needs to be addressed

don’t worry, you make plenty sense

first of all, determine how important it is to distinguish whether a phone number is a cell phone or a mobile phone, and whether you need to manage them as such or (i suspect) all you really need is a place to store one or two numbers as the contact numbers for a person

if you need something more than just two columns (main number and optional alternate), then a second table is warranted

but note that each entity type that needs multiple numbers should have its own secondary table – it’s when you go slightly past this point and try to have only one table for all phone numbers that you get messed up, because then you also have to store “which table does this number belong to, persons or businesses” i.e. metadata, and that’s generally a mistake (because it makes the queries more complicated)

same with addresses – if you have multiple addresses for a business, fine, they go in a secondary address table for businesses, and person addresses wouldn’t be in the same table (if persons even needed to have multiple addresses too)

i think you are ~way~ overcomplicating it

if you implement anything even like your first idea (id,phone,linkid,table), you are in for a world of hurt

let me take that back, since phone and person, as well as phone and company would be a many to many relationship… in rare cases. The latter would eliminate having a third individual table for each address phone and email thus combining three tables into one. however I need to figure out whether or not I want to extend it in such a way that each phone number can have multiple persons, In reality what I want is to make phone and address extended to a company and possibly an individual at the same time. which would mean I would have to make the email (id, email) along with the customer (id) unique but then that just means that all four fields need to be unique amongst themselves. so no… Ok I think I got it. In the many to many table where it is (id1, table1, id2, table2) id1(refereed as email in table1 field) , table1, and table2 would all have to be unique since id2 can refer to a different table, this would limit an individual phone number to one business, yet allow it to become a number for an individual as well, unfortunately if I encounter two businesses under the same number, (perhaps two doing buisness as) then that is out of the water…

lol sorry for the paragraph, I thought it would help. I guess I just need to make a decision and stick to it at this point.

I agree on keeping relevant information on the person only. Unfortunately I may essentially design the database however I know I won’t be able to guarantee that it will be used that way. lol

I’ve also set up my phone number table(as well as address, email, and any other table that can be related to both a company as well as a person) in the following manner.

The phone number table has fields for Phone_id, Number, ext, type(fax, home, work, cell etc…), idlink, tablelink.

So that company 444’s phone number is where idlink=444 and tablelink=company
Similarly Person 555 phone number is where idlink=555 and tablelink=phone.

I don’t think this would cause any problems (assuming that tablelink is always present) however I am not convinced that this is the most streamline way to set them up.

the company table should not have any links in it

How is this for design?

A table called reference which essentially holds all the structural links

Example
Company (id, companyname)
person (id, personname)
phone (id, phone)
ref(id1,id2,table1,table2)
where the link between a company and it’s phone number is established in ref
ref(companyid, phoneid, company, phone)
if it was an person
ref(personid,phoneid,person,phone)
extended to other tables it could mean
ref(personid,emailid,person,email)
I’m making a flowchart and if I do it this way then all the links go just to the ref table

am i better off with what I stated originally
phone(id,phone,linkid,table)

I don’t see any difference except I become heavily reliant on the reference table to make all my joins. Also I would need the reference table to know where my joins are going to since the phone table wouldn’t reveal anything to me.

I understand what you mean, I will try your approach and put the addresses and phone numbers within the same table as the entity, such as company and person. But a quick question, How do you handle say for example, faxes, cells, mobile phones, as well as shipping vs. billing address. Do you place them all in the same table and populate them when needed? I have programmed a different database before which doesn’t need to store a phone number for multiple entities but instead the numbers are stored in one table and refer to one owner, so that all the possible numbers for one entity appear whether it is a fax or a phone with a simple filter which searches for where customerid = id. I did it that way at the time since it was easier just to do a search in one field instead of linking all the different possible fields together for results.

I don’t know if any of this makes sense, if it doesn’t I can clarify

Perfect, thank you r937 and ScallioXTX, thank you for the advise and for confirming that my design is logical, now I can get this off of paper and into SQL in no time.

my rule of thumb for things like phones and addresses –

if you are interested in those entities regardless of whether someone actually has a particular phone number or address, then they deserve separate tables

for example, the phone compnay needs to keep track of all valid phone numbers (some numbers aren’t valid), whether or not someone currently uses a given phone number, and a city needs to keep track of all valid municipal addresses, whether or not someone currently occupies a given address

otherwise, phone number and address are attributes of existing entities (like companies or people) and ~not~ entities unto themselves

making separate tables for them merely over-complicates the database design

there is a common reason given for doing it, usually related to writing generic application code that can massage addresses and/or phone numbers whether they belong to a company or a person or whatever

however, this is just another example of the application tail wagging the database dog

i know ~exackly~ how you feel !!!

:smiley:

The separate many-to-many table sounds like a good solution to me, but I would take it a bit further.
What if a person has a different phone number for the different companies he/she works for? How about e-mail addresses?
In essence I’m saying only include information of persons in the person table that are true for that person regardless of the company he/she works for (like birth date and personal phone number for example) and include all other information that may be company-related in the many-to-many table.