The first step in designing a database solution is to identify its users. List all the people who use your organization's current set of tools and those who could benefit from a more efficient solution.

In this article, let's assume this database is meant for recruiters such as you, hiring managers who have open positions on their teams, and employees looking to move to a different job within the organization.

 Note   Let's leave out external candidates and interviewers to keep this design task simple. After designing and creating this database, you could extend it to include external candidates, interviewers, the personnel department, and so on.

The next step is to identify what information your users need, when and how often they need it, and in what format it should appear. It will help to look first at how these users currently meet their needs, and to find out what is not working well. For example, the hiring managers don't want to wait until Friday to see the new résumés. So, one of the requirements of the new solution is for hiring managers to be able to print résumés received for job openings in their group on a daily basis.

 Tip   A good way to make sure you identify all user needs, and the right format for each, is to create sample forms and reports and to discuss and test them with potential users. You use a form to enter data, and a report to view data. Discuss the sample data, the amount of detail, the way the data is laid out, the frequency of use, and the delivery techniques. These mockups will later help you design your forms and reports when you are creating the database.

Here are some sample needs for each database user category:

If you created sample reports and forms, list the data items in each, and then apply the first three rules of normalization (known as first normal form, second normal form, and third normal form) to arrive at data structures that avoid redundancy and inconsistency.

For example, here's what the résumé data looks like before it is normalized, and then how it looks after you apply the first, second, and third normal forms:

After optimizing the data structures, see if one or more groups (or tables) already exist in another data source, such as a database owned by another team in the organization. For example, an employee's job history and current position details might be in a database owned by the personnel department. Find out if you can simply share or link to their data to eliminate redundancy within your organization, and reduce work for yourself!

When you create the database, the complete résumé and candidate details that a hiring manager is interested in will come from more than one table — objective and job applied for from the Résumé table, candidate's contact information and preferences from the Candidate table, skills and education details from the Skill matrix and Education details tables, and so on. Before generating a list of résumés for a hiring manager, these tables must be joined in a meaningful way so that all relevant information about a specific candidate (or employee) can be brought together. Identify tables that must be related, and the fields that you will use to establish the relationship. For example, Résumé and Candidate tables can be related using the common field Candidates ID. The Candidate and Skill matrix tables can be related using the common field Employee ID.

If you have sample forms and reports, use them to finalize the forms and reports that you need to create in the database. Choose the layout of the data fields, identify the underlying tables, pick a theme, and so on. For example, the data that a candidate enters in the Résumé form will be stored in the Résumé and Candidate tables. The data in the report on the number of positions filled during the past week will come from the Jobs table. Make sure you design all the forms that users will need to input data, the printed reports, and the queries.