Two Minute Tuesday: Relational Database Structure

Video Transcript

Welcome to this week’s Two Minute Tuesday, Main Sequence’s series of short videos with tips, tricks, and tutorials to make you a more powerful PCRecruiter user.

We’ve been together in this video series for a few months now, and we think it’s time to discuss the relationship. Specifically, the relationship between the major record types in PCRecruiter. While most users will never need to or want to know how the SQL database under the hood of PCR is laid out, a mental picture of how records are related can be helpful – particularly if you’re building an advanced search query, custom report, or planning a development project using the PCRecruiter API.

PCRecruiter uses a relational database with multiple tables to contain different kinds of information. For example, there is a table for companies, a table for names, and a table for jobs. Each table has columns of data for the specific attributes of that record type, such as a city, title, middle name, and so on.

Every record in the main tables has a unique global identifier so that the system can track which records are related to each other and how. Setting things up this way allows the same piece of information to be used in relation to multiple records, or to be easily re-associated with a different record at any time.

PCRecruiter is somewhat unique among recruiting systems because it uses the Company record as the central record type, although a company record can really represent any group of related names or jobs, not just an actual physical company. While it may appear that the names and jobs are ‘part of the company,’ the Company record itself just contains the info about the company. The people and jobs associated with that company are in the Name and Position tables. So, when you view the names belonging to a company, PCR is actually querying the Name table of the database for any records that match the global identifier of the selected company.

The critical advantage here is that a name can be moved from one company to another simply by changing the company identifier, allowing a candidate to become a client, or a vendor to become an employee, without duplicating or recreating their contact record. All of that person’s activity logs and attachments (which, incidentally are kept in their own separate tables), stay linked to that name no matter what jobs or company the name is associated with. Over time, PCR’s company-centric database structure lets you build up a much clearer picture of where your contacts have been and how they relate to one another.

The connection between jobs and candidates is managed in the same way. When you view an applicant Pipeline, you’re looking at records from the Interviews table. Interview records (which in PCRecruiter terms represent any step in the recruiting process – not just phone and face-to-face interviews) include things like the date, time, and status of that step in the process, but they don’t include the information about the job or the candidate. Instead, they include the global identifiers indicating which job and which names those pipeline records are pertinent to. This setup lets you have multiple candidates tied to multiple jobs, regardless of what company the job or candidate are associated with.

In fact, most of the time your candidates will belong to the ‘default company’, which is a special company record that PCR uses to group all the names that don’t belong to any more specific company. A talent pool. When you place someone, PCR changes their company identifier to the one from the job they were just placed in, effectively making them a name under that company, but without losing or changing any of the history they came with.

Fortunately, as you interact with your database, whether that’s through PCR itself or a third-party tool using the API, the software builds all the structured queries necessary to navigate the database and return the info you want, whether or not you fully understand how it’s all connected. If you do need more specifics on the tables in a PCRecruiter database, contact our support team at support@mainsequence.net.

And for more Two Minute Tuesdays, subscribe to this YouTube channel and watch our social networks or your PCR login screen. If you have any suggestions for future episodes, send an email to twominutetuesday@mainsequence.net.