Skip to content
Advertisement

1:N relationship to profile or user table?

Suppose I have a User Table and a UserProfile Table that I have separated. The two tables have a 1:1 relationship.
User table contains only data like email and password, while UserProfile contains data like first name, last name, phone number, etc.

Does it make more sense that future tables like Projects (for projects of a user) or Ratings (ratings of a user) have a 1:N ratio to the UserProfile table or the actual Account table (User)?

Thanks for tips.

Advertisement

Answer

Your users table should have a unique id to identify users throughout your database. In general, this would be an integer, because those are a bit more efficient for indexes.

As for your data model, you would use this user_id for all tables that reference users. This includes userProfiles. In that table, it would be both a primary key and foreign key — unless this were a type-2 table with version effective and end dates.

Note: I would tend to put all sensitive data in userProfiles — including the email. An encrypted password would not need to go there. That way, you can better control access to PII (“personally identifiable information”).

User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement