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

A user is a user, which is different than a person.

A user is a role played by a person. A recruiter is also a role played by a person.

create table party(
  id serial primary key,
  name text not null
);


create table role_type(
  id char(1) primary key,
  name text not null unique
);


insert into role_type values 
('u', 'user'),
('r', 'recruiter');

/* use Table Inheritance to add role-specific details. */
create table party_role(
  party_id integer not null references party(id),
  role_type char(1) not null references role_type(id),
  ...
  primary key (party_id, role_type)
);

/*  create a user: */

insert into party values
(1, 'Neil');
insert into party_role values
(1, 'u');

/* create someone that is both a recruiter and user: */
insert into party values
(2, 'Tristan');
insert into party_role values 
(2, 'u'),
(2, 'r');

Answer 2

I would go with your second suggestion simply because there is no duplicate data in that model. Check Database normalization.

Joins won't be expensive if you put index on user_id, which you anyway should, since it looks like primary key.