2

In AdonisJs framework, suppose we have a Profile model in which we define belongsTo() relationship to theUser model. The documentation says this will lead us to define our databases this way:

enter image description here

The column id in users table is a foreign key referencing profiles tables instead of being the primary key of users table. Can this 'bad DB design' lead to some inconsistencies?

Billal Begueradj

  • 1
    I think things are the wrong way round? Users can have more than 1 profile, but a given profile can only belong to 1 user? If so, you should have the user_id field in the user table as the PK of the user table and the FK should be the user_id field in the profile table. – Vérace Feb 27 at 11:50
  • I took the example as it is from the documentation I linked to, and I agree that is not a good example, but let us accept it as it is, should not we keep id in users table a primary key, and user_id a foreign key in profiles table? – Begueradj Feb 27 at 11:57
  • That depends on the circumstances. If 1 user can have many profiles but 1 profile can only have 1 user, then your diagram is flawed. If a profile has many users and 1 user can only have one profile (parent), then your diagram is OK. If many users can have many profiles, then you need a joining table (or associative entity). HTH... – Vérace Feb 27 at 12:03
1

I think that the only problem with the linked documentation are the typos in the model image:

The users column id (FK) was meant to say id (PK).
The profiles column user_id (PK) was meant to say user_id (FK).
The link should have reverse direction (in all other diagrams, links start from the PK/referenced table and point to the Fk/referencing table).

The diagram under the belongsTo() example seems to be the correct one (and should be used in hasOne(), too).

The design shows a 1-1 relationship, or more accurately a 1 - 0..1 relationship, between users and profiles.

A user can have one (hasOne) profile. A profile belongs to (belongsTo) one user.


What seems to be missing from the diagrams is that profiles (user_id) should have a UNIQUE constraint, in order for the relationship to be true 1-1 and not 1-to-many. Perhaps the ORM creates such a constraint and it was missed when drawing the diagrams.

  • Your answer makes sense. I hope it is a question of typos in that documentation, otherwise my mind can not swallow that diagram, I will try to contact the 2 permanent maintainers of that framework. Thank you very much – Begueradj Feb 27 at 13:32
  • You can send them a bug report. The diagram in the hasOne() seems to be the correct (and should be used in the belonsgTo() case, too, as it is) – ypercubeᵀᴹ Feb 27 at 13:33
0

Firstly, you have to establish the cardinality of the relationships.

If you have users who can have many profiles, and a given profile can only belong to one user, then you should have

CREATE TABLE user  -- all fields are INTs unless otherwise indicated
(
  user_id CONSTRAINT user_pk PRIMARY KEY, -- <<=== normal practice is to have a surrogate key - AUTO_GEN or SEQUENCES or similar
  user_name VARCHAR,
  ...,
  ..., -- other user stuff...
  ..
);

CREATE TABLE profile
(
  profile_id CONSTRAINT profile_pk PRIMARY KEY, 
  user_id,
  CONSTRAINT profile_user_fk FOREIGN KEY (user_id) REFERENCES user (user_id),
  -- exact syntax may vary...
  ... other stuff
);

If you have many users who each may have many profiles (think of the users here on StackExchange/StackOverflow where you can have a different profile per site), you would have an Associative Entity (a fancy name for joining table - see link for other names) which would look this this:

CREATE TABLE user
(
  user_id,
  ... other stuff
);

CREATE TABLE profile
(
  profile_id,
  ... other stuff
);

CREATE TABLE user_profile
(
  user_id,
  profile_id
  CONSTRAINT up_user_fk FOREIGN KEY (user_id) REFERENCES user (user_id),
  CONSTRAINT up_profile_fk FOREIGN KEY (profile_id) REFERENCES profile (profile_id)
); -- <<== again, syntax may vary

In your own particular case, where a given user can have one and only one profile, you could have:

CREATE TABLE user
(
  user_id PK, -- plus other fields
  profile_id CONSTRAINT user_profile_fk FOREIGN KEY REFERENCES profile (profile_id),
  -- ...
);

It appears to me more likely that a given user could have more than one profile, so you might wish to exclude the FK in the user table? This will make it easier to add multiple profiles later should you be looking at this possibility.

CREATE TABLE profile
(
  profile_id PK, -- plus other fields
  user_id CONSTRAINT profile_user_fk FOREIGN KEY REFERENCES user (user_id),
  -- ...
);

i.e. there is a reciprocal relationship between user and profile. This will, however create major problems with MySQL which doesn't support DEFERRED CONSTRAINTs - see here (as of Feb 2019, the latest version of MySQL):

Like MySQL in general, in an SQL statement that inserts, deletes, or updates many rows, InnoDB checks UNIQUE and FOREIGN KEY constraints row-by-row. When performing foreign key checks, InnoDB sets shared row-level locks on child or parent records it has to look at. InnoDB checks foreign key constraints immediately; the check is not deferred to transaction commit. According to the SQL standard, the default behavior should be deferred checking. That is, constraints are only checked after the entire SQL statement has been processed. Until InnoDB implements deferred constraint checking, some things are impossible, such as deleting a record that refers to itself using a foreign key.

If there is a strict and never to be changed 1 <--> 1 relationship between the entities, you may wish to ask yourself the question "Why do I have two tables?". It may be "tidier" that way and or other factors may come into play - but it's worth considering? You (again) may be looking at redesigning later on, so it may be a good choice to split the tables now.

As for the question, there is absolutely no valid reason to have user_id as the PRIMARY KEY of the profile table. It makes no sense.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.