2

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!

0

2 Answers 2

1

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_id and 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.

1

You have an option to create two tables with user and profile data or together as one table with both the data. This depends upon some factors.

Two table scenario:

In case of large set of fields and the data is accessed often from a subset of these fields, two tables can be used. The frequently used data is the primary table. Data security is another factor, and certain data can be stored in a separate table and restrict access to that table from the database and application users. This also makes sense, when you want to separate data logically. These have 1-1 relationship.

What data goes into which table is a matter of your application and data requirements. You have one table as primary table and the other as secondary. You link the primary table to other tables like transactions or payments to in a one-to-many relationship.

One table scenario:

In case of one table only (refer it as users), you can separate data within the same table. The less accessed data can be stored as JSON type. This data can be a JSON Array in case the users data has one-to-many relationship between profile/user or user/profile. The primary data has the one side of the relationship. JSON data type is supported by popular databases like, e.g., MySQL and PostgreSQL. A NoSQL Document database works fine too.

If security is a concern you can implement redaction feature, restricting access to some fields in the table.

You can link users to transactions or payments with 1 (users) - N (payments) relationship.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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