Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
I noticed this is pretty similar dba.stackexchange.com/questions/1971/… except that in our case an employer can also have personal page. We are more between LinkedIn where all the recruiters have a personal account and this example. –  Tristan Feb 26 '13 at 9:41

2 Answers 2

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 the 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');
share|improve this answer
    
Thank you for your answer, but how about if I want to get the information concerning a recruiter in a big database? I would need to do a JOIN on the party_role table to check if a user is actually a recruiter. If that table is very large, this seem to be quite expensive. –  Tristan Feb 25 '13 at 19:47
    
Yes, you would need to do a join, but no, joins are not very expensive in modern relational databases. It is also far less costly than having duplicated data all over the place. You can use Single Table Inheritance for the party_role table to minimize joins if you like. –  Neil McGuigan Feb 25 '13 at 20:30
    
How about database sharding in this situation? Thanks! –  Tristan Feb 26 '13 at 14:05

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.

share|improve this answer
    
This seems definitely more elegant indeed but it would mean 1/ two different teams would have to work with the same database 2/ To get the recruiters data we would need to query the main user database. 3/ To query the user pages we would need to take off the non-recruiters. –  Tristan Feb 26 '13 at 10:52
    
1) I don't see why would this be a problem? With first solution you still need someone to coordinate changes between these tables. 2) Are these tables in the same database? Which RDBMS are you using? But I still don't see the problem with this either. 3) You can have a flag in users table which tells you is that a "normal" user or a recruiter, if that's easier for you. –  bojan Feb 26 '13 at 12:42
    
We are using MySQL and the tables are in the same database for now but how about database sharding if we reach a large number of users? We would then have separate databases and that will become a problem. The recruiters table on the other hand won't need to be sharded because their number will stay in the 100'000s rows max. –  Tristan Feb 26 '13 at 14:04
    
I never worked with MySQL Cluster, but AFAIK, database sharding is horizontal partitioning (by rows), not vertical (by columns). Therefore, you would still need same database schema on each node. It makes no sense to design database differently to "support sharding". –  bojan Feb 27 '13 at 0:07

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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