I'm migrating data from a schema where people and organisations are separate tables to one where people and organisations are all treated as contacts (they share a lot in common). Currently, the people table has about 90k records with 80k relationships to 10k organisations.
Contact Relationship (Contact table again) -------- ------------- --------------------- cid 11----0< cid_a /---11 cid name cid_b >0-/ name details start_date end_date relationship_type
If I want to be able to query for Wilma's current relationships (let's say Wilma has
cid = 2) I can set up 2 keys on
(cid_a, cid_b) and
SELECT friend.name FROM contact friend, relationship WHERE ( ( cid_a = 2 AND cid_b = friend.cid ) OR ( cid_b = 2 AND cid_a = friend.cid ) ) AND ( start_date IS NULL OR start_date <= CURRENT_DATE ) AND ( end_date IS NULL OR end_date >= CURRENT_DATE )
But I'm not sure it's efficient as the duplicate keys would be quite long.
A contact will likely have 3, 4 or more relationships to various organisations, other contacts etc. such as
- Wilma is a student at X University-
- Wilma is a member of Y organisation
- Wilma was previously a contact at Z organisation
- Wilma is married to Fred.
Is this the One True Way? Or nothing like it?!