Im sure this is more simple then I am making it in my head but I am hitting a wall. i have been playing with multi key relationships and some other options but I keep running into problems. I plan on created an "adder" table that i will vacate after each new contact is selected to be added and the relationship text is filled out.
I have a table contactCompany and a table contactCompantRelationships
My companies and contacts are in the same table on purpose and my solution needs to remain that way. I know there are other ways to set that up but it is valuable for me.
I want to be able to say
Dave is the owner of company ABC
Nick is an employee of company ABC
Nick used to work for company XYZ
Dave are enemies joe
When a users views daves contactCompany record, they should see the relationship record that names him the owner of company ABC and one that says is an enemy of contactcompany with the name Joe
when a user views nick , they should see that he works for ABC and used to work for XYZ
when a user views XYZ they should see nick uses to work for them
viewing ABC they should see dave is the owner and nick is an employee
viewing joe displays that dave is his enemy
Thank you in advance