Database model with circular references, can it be avoided?

Question

I have the following tables:

• Person
• Membership
• person_membership (for many-to-many between person and membership)
• Relationship

Now a Person can be in one or more memberships. Each person can be related to another person in a membership.

To make the above working, I put the below fields in the Relationship table:

• id
• person1_id
• person2_id
• membership_id

As you can see, the above will end up with a circular references between Membership, Person and Relationship. Is there a way or a better design to avoid that?

Ex: John and Marry are in membership 1234 John and Adam are in membership 5678

In membership 1234, John is husband of Marry

In membership 5678, John is brother of Adam

Answer 1

There is no technical reason to avoid circular references in a relational data model. SQL handles it just fine, and it is good practice to model the relationship accordingly to represent the entities in the same table if they are the same kind of entity. There are even special keywords in some database flavors to support recursive/hierarchical relationships.

If it makes you uncomfortable, just get used to aliasing your tables in SQL queries, such as when you access the Person table for different reasons (at different levels), to represent the level of the access.

In other words, lets assume Person has a parent field. Every Person record will recursively point to a parent record.

select Children.*, Parent.name from person Children
  left join person Parents
    on Children.parentID = Parents.ID

Now it is as if you are selecting from and joining 2 different tables. SQL doesn't care how many times you reference the same table in a query, it treats it as if it is a unique table each time (for the purpose of what I am saying). You can select from Person as p1, p2, p3, p4, etc. and join them all however you need to.

The problems that crop up if you try to rid yourself of circular references are myriad, depending on requirements down the road, and they usually don't perform as well. A single Person table will perform well due to buffer cache locality. If you tried to break out Person table into different functional roles, you inevitably run into some requirement where you need the same person in multiple tables, and you end up duplicating data.

Views are another useful tool to simplify circular references, depending if you can assign role or record type identifiers to Person. A view can slice / filter the base table by function, in many cases.