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:
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
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.
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.
