MySQL Circular References in One-to-Many Relation: Is there a way to avoid it?

Consider following as an example:

I have a User table which contains user’s information along with a PrimaryAddress column that references Address table.

The Address table, contains address information along with a UserId column which refers to who the address belongs to.

Each user can have many addresses, but only one address can be PrimaryAddress. Therefore, the User table needs to store a reference to PrimaryAddress to enforce this rule. Having a IsPrimary column in address table would not have a similar effect.

But as you can tell, this will create a circular relation between User and Address tables and circular dependencies can be a sign of bad design as far as I’m aware. The question is, is there a way to avoid this? If so, how?

Answer

A circular reference is not necessarily a “bad” design. You gave an example of a real-world case that has legitimate meaning.

I admit it’s a little bit complex to manage. The User.PrimaryAddress must be nullable, if you need to create a user row before you create the address you will eventually designate as the primary address.

Also if you need an SQL script to recreate the database, you can add foreign key constraints only after the referenced table is created. So you have a chicken-and-egg problem if you have circular references. The typical solution is to create tables without their foreign keys, then once they are created, add the foreign keys using ALTER TABLE.

The workarounds all sacrifice something. If you add an attribute Address.IsPrimary, then you have to figure out how to ensure that exactly one address per user is primary. If you use a third table, you have to worry that it is missing a row for each user.

The circular reference may be the least problematic solution.