I know it is a common paradigm to separate tables when building a User profile. For example, having a user table, and another table called user_profile with a foreign key to the user table.
My understanding is that the user table is better for sensitive user account data or authentication data, such as email, password, user type etc. The Profile table could have additional data of that user like first name, last name, date of birth and more.
But, what about other data that can be related to the user, and can also be modeled with a table. Some examples could be Payments and Transactions. My first guess is to link those to the profile table and that way I don't have to make any joins just to have Transactions and the name of User together (which the user table does not have). Linking to the profile, I have the user info and the Transactions. But then, when is it useful to link to the user table? What are the common paradigms?
Thanks in advance!
Best Answer
If both tables - user and user_profile - have as primary key the column user_id, and there is a one-to-one mapping between rows in these tables, what you have there is a vertical partitioning of a single logical entity.
In the logical data model this should be represented as a single entity type. In the physical data model that single entity type is implemented as two tables. There are reasons why you would want to do this. Security is one, as you have mentioned, though that could be handled just as well with views.
I have not come across this implementation nor seen articles advocating it. I'm not convinced this paradigm is that common.
If there could be many rows in user_profile for each row in user then you have a simple parent-child pattern. Other tables will follow the usual pattern, too.
For other tables which have obvious one-to-many relationships to user your options are not a simple binary. In the logical model the situation is simple - entity type Payments references entity type User. In the physical implementation with vertically partitioned user table you have options. If either of user or user_profile is optional the foreign key must be declared as referencing the mandatory one. If both are required then it simply doesn't matter.
To be explicit, it is OK to join in columns which are not explicitly declared as foreign keys. So you could have the DRI written as
.. payments.user_id references user.user_idand legitimately write.. from user_profile up inner join payments p on up.user_id = p.user_id. That is OK; it will work.If you really wanted to, and your DBMS didn't object, you could even declare two foreign keys for the child table. One FK will link Payment to User. The second will link Payment to User_Profile. My feeling is that this is excessive and adds little, but it is not "wrong".
To answer the stated question, I would follow the principle of least surprise. If table Payment has a column user_id, and there exists a table called User I would expect Payment.user_id to reference User.user_id.