Should I create a separate user table for different web products within the same platform?

Question

We have a web product for young professional that gives them the possibility to create their page to show their professional identity. So a table users that has both information about the user (email, password, name) including their credentials and information about their page (premium or not, page address, theme)

Now we want to offer the possibility for recruiters to signup to our platform to browse through candidates. A recruiter can also be a user with a page but does not have to.

Now our two approaches:

A/ Create a table recruiters with name and credentials of the recruiter and a column user_id to connect with the ID of the table users if they have created a site.

• Benefits : The product can be easily developed separately, by two different teams.
• Inconvenient : Duplicates of the name and credentials if the recruiter is also a user. We would need to either update both credential when one is updated or to let them have two different email/password combination, one for their user account, one for their recruiter account.

Database structure:

users
ID name email password group_id premium theme page_address

recruiters
ID name email password company_id user_id

B/ Add the recruiters to the users table with a different group_id and move all the information about the users page in another table (premium or not, page address, theme). We would also have a third table for the recruiter containing any information specific to them.

• Benefits : One table with all the credentials.
• Inconvenient : If we reach millions of users, any query among recruiters will have to take a tiny subset among a huge table. Also : lots of join to get the site information for every user.

Database structure:

users
ID name email password group_id

pages
user_id premium theme page_address

recruiters
user_id company_id

C/ Any other solution?

Thank you for your inputs!

Tristan

Answer 1

Relational database architecture is not an exact science, but you can relay on a few common guidelines to make good decisions. In the first option you present, it can be easily identified that there's a situation of column duplication. Few of your columns have the exact same meaning on a conceptual point of view. The password column is used to log on your website whether or not you're a recruiter. This wouldn't always be considered an issue, but it's a great hint that there should be a better relational pattern to design your data schema.

A great approach to resolve such concerns is to establish conceptual relations between objects. For instance :

• Users are or aren't Recruiters would be a 0..1 <-> 1 relationship or an optional One to One
• Pages belongs to a Users would be a 1 <-> 1 relationship or One to One
• Recruiters might have a Pages would be a 0..1 <-> 1 relationship or an optional One to One

This exercise helps you to understand how to list your entities and organize your foreign keys. This is a good first step that, in your situation, gives us three tables : Users, Recruiters and Pages. Notice how foreign keys for One to One relationships were placed in the mandatory 1 cardinality tables.

Now to determine where to place your data columns and whether or not you should have new tables : an easy trick would be to write everything your denormalized entity would have as such by targeting the most derivated entity you have.

This example is pretty obvious, but I still think it's answering your question and your doubt regarding a group entity and columns duplication.

At this point I realized I forgot to include the Companies entity that would be stated as such :

• Companies can have multiple Recruiters would be a 1..* <-> 1 relationship or a One to Many

Once again, keep in mind people might not always agree with this approach, but considering this small context; it was an easy train of thoughts. The answer you would have been looking for is : Column's meaning duplication is a hint you should approach your data schema differently. Here's a fiddle.

If you have any questions or feel this is wrong feel free to comment!