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

Question

We have a web product for a young professional who gives them the opportunity to create their own page to show their professional identity. Thus, the users table, which has both user information (email address, password, name), including their credentials and information about their page (premium or not, page address, subject)

Now we want to invite recruiters to register on our platform to view candidates. A recruiter may also be a user with a page, but is not required.

Now our two approaches:

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

• Advantages: the product can be easily developed separately, by two different teams.
• Inconvenient: duplicate name and credentials if the recruiter is also a user. We will need to either update both credentials if they are updated, or give them two different email / password combinations, one for the user account, one for our 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 recruiters to the users table using another group_id and move all the information about the users page to another table (premium or not, page address, subject). We will also have a third table for a recruiter containing any information related to them.

• Advantages: one table with all credentials.
• Inconvenient: if we reach millions of users, any request among recruiters will have to take a tiny subset among a huge table. In addition: a lot of connections to get site information for each 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?

Thanks for your details!

Tristan

Answer 1

The relational architecture of the database is not an exact science, but you can convey a few general recommendations for making the right decisions. In the first option that you present, you can easily determine that there is a situation of duplicate columns. Few of your columns have the same meaning conceptually. The password column is used to enter your site, regardless of whether you are a recruiter. This will not always be considered a problem, but it is a big hint that there should be a better relational template for developing your data schema.

An excellent approach to solving such problems is to establish conceptual relationships between objects. For instance:

• Users are or are not Recruiters will be 0..1 <-> 1 relationship or optional one to one
• Pages owned by Users will be 1 <-> 1 ratio or one to one
• Recruiters may have a Pages ratio of 0..1 <-> 1 or optional one to one

This exercise will help you understand how to list your entities and organize foreign keys. This is a good first step, which in your situation gives us three tables: Users , Recruiters and Pages . Please note that foreign keys for One to One have been placed in the required 1 cardinality .

Now, to determine where to place the data columns, and whether you should have new tables: a simple trick would be to write all your denormalized ones will have as such, focusing on the most educated object that you have.

This example is pretty obvious, but I still think it answers your question and your doubts about duplicating groups and columns.

At this point, I realized that I forgot to include the Companies object, which will be listed as such:

• Companies may have several Recruiters - a ratio of 1..* <-> 1 or from one to a large

Remember again that people may not always agree with this approach, but given this small context; it was a simple train of thought. The answer you would be looking for: Column means duplication - this is a hint that you should approach your data schema differently. Here's the fiddle .

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