CODEWITHSUNDEEP
mysqlsql-serverdatabaseforeign-keyscyclic-reference
Dilnoor Singh
Dilnoor Singh

Reputation: 518

Foreign Key cyclic reference Dilemma

Consider a simple situation in which there are 2 tables, named Users and WorkGroups.

  1. A User's email address is the primary key in the users table.
  2. A Workgroup_id is the primary key in the WorkGroup table.
  3. A user can create multiple workgroups.
  4. A user can be part of just 1 workgroup.

Under this given scenario I need to track which user created a workgroup.

What I have already done:

  1. Have a variable named workgroup_id in the users table to know which workgroup the user belongs to. This is a foreign key to the workgroup_id in the Workgroup table.
  2. Have a variable named user_email in the workgroup table to track which user created the workgroup. This is a foreign key to the user_email in the users table.

The problem which I am facing here is that this results in a cyclic reference between the users and the workgroups table. Since cyclic references anywhere in programming are a big no no.

How do I solve this? Is there a better design pattern which I am missing here?

EDIT: As for whether the "circular references are a big no no" or not, conceptually they may not be but since there implementation is non universal in different databases, they still remain a valid problem. This is aggravated by the case when you have use an ORM, where the ORM support for your database limits the kind of database design you can have.

Upvotes: 1

Views: 870

Answers (1)

Barmar
Barmar

Reputation: 780714

You need to allow at least one of the foreign keys to be NULL. This will allow you to create the first row in that table, leaving the foreign key empty as a placeholder. After you create the corresponding row on the other table, you update the foreign key in the first row.

You could also decide that this is OK as a permanent condition. If you create the first workgroup automatically before creating any users, that first workgroup doesn't really have a creator, so you could leave it set to NULL.

Upvotes: 3

Related Questions