1

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.

Dilnoor Singh
  • 518
  • 5
  • 16
  • I don't see how this is cyclic. The user who creates the workgroup and the users in the workgroup are not related. You may want to look into identifying vs non-identifying relationships: https://stackoverflow.com/questions/762937/whats-the-difference-between-identifying-and-non-identifying-relationships – dragmosh Sep 13 '17 at 21:26
  • 1
    @dragmosh While it's not cyclic in that way, there's still a chicken-and-egg problem. He can't create the first workgroup, because it needs a creator email. And he can't create the first user because it needs a workgroup ID. – Barmar Sep 13 '17 at 21:51
  • Although it probably don't make too much of a difference in a design question like this, you probably don't want to tag this with both `mysql` and `sql server` as they are not the same product. – Xedni Sep 13 '17 at 21:55
  • @Barmar In this model the user shouldn't have to be in a workgroup. The user who creates the first workgroup can just have a null workgroup_id. – dragmosh Sep 13 '17 at 21:57
  • "Since cyclic references anywhere in programming are a big no no." That's nonsense. – philipxy Sep 14 '17 at 04:15
  • Possible duplicate of [Circular dependencies in foreign keys: use it or avoid it?](https://stackoverflow.com/questions/3891535/circular-dependencies-in-foreign-keys-use-it-or-avoid-it) – philipxy Sep 14 '17 at 04:17

1 Answers1

3

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.

Barmar
  • 741,623
  • 53
  • 500
  • 612
  • Thank You Barmar, that's in fact, a great solution. But what do you think of foreign keys in terms of rows, not columns (in the question's context only)? As in this case, the user who creates a workgroup will always exist before the workgroup itself. Adding and Deleting the workgroup won't be a problem. So would the structure still be in circular referencing state? – Dilnoor Singh Sep 14 '17 at 08:58
  • Then the nullable column should be `users.workgroup`, since the first user has to be created before the workgroup that he's a member of. – Barmar Sep 14 '17 at 15:18