CODEWITHSUNDEEP
mysqlsqldatabasedatabase-design
David542
David542

Reputation: 110572

Using circular FK references in a database

I have two (mysql) tables -- company and user. They are structured as follows:

`user`
- id
- company_id (FK to company)
- name

`company`
- id
- name
- admin_user_id (FK to user)

The user table foreign keys to the company table, and vice versa.

I was wondering if the above pattern is ok, and if it's not why it's not, what could be done to improve it.

Upvotes: 2

Views: 82

Answers (2)

nvogel
nvogel

Reputation: 25534

If it is an accurate representation of your business domain then that's what really matters. There is a possible problem in SQL however. SQL only allows one table to be updated at once so either company or user has to come first. Normally you have to allow the constraint to be broken temporarily, either when you insert a new company with no corresponding user, or insert a new user with no corresponding company. To achieve that you can make one of the foreign keys nullable or you can temporarily disable the constraint.

Some data modellers dislike circular dependencies, even in purely conceptual models where the limitations of SQL are irrelevant. A few people will perceive circular dependencies as a modelling mistake - wrongly in my opinion. Disapproval of circular dependencies seems to be associated with ER modelling specifically. Interestingly, circular self-referential dependencies are taken for granted in Object Role Modelling which even has a special notation for them (ring constraints).

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1271151

At a high level, your data model makes sense. However, you have no guarantee that admin_user_id points to a user in the same company. You can solve this by doing:

create table users (
    user_id int auto_increment primary key,
    company_id int,
    name varchar(255),
    unique (company_id, user_id)  -- redundant but desirable for the foreign key reference
);

create table companies (
    company_id int auto_increment primary key,
    name varchar(255),
    admin_user_id int,
    foreign key (company_id, admin_user_id) references users(company_id, user_id)
);

alter table users
    add constraint fk_users_company_id
        foreign key (company_id) references companies (company_id);

Upvotes: 3

Related Questions