December 18, 2007 at 8:54 am
I'm currently building a jobs portal which will allow candidates to post their details online and apply for jobs posted by our company. It is divided into two applications, one is the jobs portal which is publicly accessible and candidates can register and apply for a specific job. And another internal application where the recruiter or consultant do all the necessary business procedures to poach candidates for a certain job or position.
Right now I'm stuck as to how would I design the database structure for the Candidates, I was thinking whether it would be better for both applications to use the same database and same tables or should I have a separate database for each application and just transition all the candidate details from the jobs portal database to the internal app database. If I would opt for the second option, I have to make sure that any changes from the jobs portal would reflect on the internal application database, and any update from the internal application database should not reflect on the jobs portal database.
Or are there any other better option than what I mentioned? I really can't make up my mind, I was hoping to get some advice from you guys, especially those have encountered the same situation.
Any inputs would be greatly appreciated.
Thanks!
December 18, 2007 at 9:13 am
I would use one database to house candidate information. SQL Server is designed for concurrency.
If recruiters are not changing candidate data you could mirror the database or use transactional replication for the candidate data.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 18, 2007 at 8:57 pm
Thanks for your suggestion.
There is a chance that a recruiter might change the candidate's data, but most of the time other tables related to the
Candidates table are being updated like Interviews, Reference Checks etc etc.
I have already check out Replication, and it seems that merge replication is more appropriate since both applications update(INSERT, UPDATE, DELETE) the candidate's tables. But I haven't really tried doing any replication before, quite new to this and not quite sure how would it impact the existing database structure that the internal application is using specifically on the identity fields and relationships to other tables.
BTW below is the list of Candidates Tables that will be shared by the Jobs Portal and the internal application the recruiters will be using:
- Candidates
- Candidates_Employment
- Candidates_Education
- Candidates_Reference
- Candidates_Family
- Candidates_Language
For the internal application there are even more additional related tables like Candidates_Remarks, Candidates_ReferenceChecks & Candidates_Interviews.
Your inputs were greatly appreciated.
I guess it is easier to manage if I'll just have both applications use the same database and tables.
Thanks
December 19, 2007 at 5:26 am
One thing we've found that is a great advantage in 2005 when working with different functionality within a single app, is to take advantage of schemas. In your case, you can certainly seperate the Candidate schema from other functions. This allows for easier seperation of storage, easier seperation of security and a logical split within the database which helps make for better programs.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 19, 2007 at 8:57 am
Thanks Grant, I'll take note of that and check it out later.
Thanks!
December 21, 2007 at 12:01 am
Hi
Going with single database is the better option. You can avoid porting/replication or any other methodology for keeping the 2 databases in sync. Also you can have reusabality of the database code that you write.
If the data volume is not high and there are no other strong reasons for having 2 different databases go for the single database.
"Keep Trying"
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply