So in this post, I will cover what I did for a data model on the resume part of the site. When I was looking into this part, I was looking to see what I needed, or what needed to happen for this to be effective and capable of doing what I wanted. So I started to look at my own resume, and some of the other sites out there that do this. In any application, it is important to understand exactly what data needs to be captured and why. I think that is where most people usually skimp on, is the why. They think once the data is identified, then that is all they need. but it is not so. One need to understand the “why” of the data in order to properly map and design the DB schema.

Take for example a simple address. It consists of a name it belongs to, number, street name, may or may not contain an apartment/suite number (address line 2), city, state and zip code. Now if we have this information we could maybe say that the name is part of a “Persons” table, and they would be linked to the “Address” table where each field is contained. And normally that may work. But why are you collecting this data in the first place? Is it a basic business application to store customer shipping addresses? Or is it for the city planning, emergency response and police units? Could it be for a post office application, or even political boundary application for a government unit? Each one of these questions may alter the way the data is stored, drastically. For a basic customer database, you could get away with two tables, and in the “Address” table you have one field for address1, address2, and so on. For a city-use application, that may not be normalized enough, and you may have to switch out to three or four tables, and break up address fields into three or more including one field for the number, one for the street name, one for the direction of the street, and one for the type of street. And they may be linked to other tables that contain that data. So just knowing that you have the data identified is not the end. You need to understand the “why”.

So, now we go on to this resume application. And remember, I am building this for me, and only me. I am not building a resume repository so others can create theirs and post it online and via Facebook. This is applicable to me. So I need to find the data that will work for me. I also need to find the data that will work for the people looking to hire people like me. What do they want to see, and why? So after I searched through to see where that would be, I came to the following conclusions. . .

A resume is focused on the job that is being applied for. Which means, if I want to apply for a Development job, then I need my resume to show data specific to a development job. I need to call out my experience and what I have done, accomplished, and so forth. If I want to apply for a management role, then I need a resume that displays those attributes. So I need a way were I would have a main resume, always available and sort of universal that will cover an abbreviated work history. I also need a way to build a specific resume and have that available. This will be the central glue that will gather the other areas together. This will house the following main data:
id
info – quick one line so that I can identify the resume
intro – a paragraph that introduces me for the job, not the cover letter
slug – for pretty urls
active – to display or not display
created, modified

Before I go into the different items that will be attached to a resume, lets first tackle those that are truly universal. One is my schooling. No matter what job I may be going for, it will not change what school I went to, or what degree, graduation status, etc. So that is one entity. Certifications/Memberships is another. No matter what job, I will always have the same certs and memberships. So that is another entity. Then there is skills. I will have the same skills no matter what position. But that can be broken down in to three entities: Skills, Levels, Areas. Skills is the main area, Level is what level I am at and can be considered a “lookup table” for Skills. Areas would house the area that expertise falls in, like PHP for programming, Zend Studio for IDEs, and so on. This would be another lookup table. So for each of these areas, the data model would look like the following:

The data model I am using comes from Visual Paradigm, and some of the data types (like blob) are not exactly matching up with what I want for MySQL. Blob is the Text equivalent in the data modeling application. Click the image to get the larger size.

Remember, this is for me, and I am sure I could have normalized this further and done a few different things. And that is fine, If you want to do it differently, then that is ok as well. This is just my approach to the solution. Next though, is where we need to figure things out.

Resumes consists of jobs and the tasks (and accomplishments). It also contains a cover letter. And in each job, I may have done a few things in that job that may pertain to the position I am looking at now. So in one of my previous jobs, I may have had some PM experience that I would need for one possible position, but not that important in another possible position. So Jobs needs to remain universal, but the tasks associated to that job will need to link to both the job and the resume. Covers will need to link to the resume, as each cover should be specific to the position being applied.

For the jobs table, I examined what I wanted to store there and why. A job is a job, and associated with some sort of company name. I may even have been place at that job via a head-hunter agency or as a contractor with a third party company. So that needs to be associated with the job. Location is another big thing, same with start date and end date. Each job will have some sort of task. I also decided I was going to assign the name of the Position to a task. And all tasks for that position will be listed as one entry to the task in the form of a text field. I also want to provide a sort order for the task details, as I may not enter them in the application in order, so I will want to be able to sort that on the backend when it is displayed.

Now we can examine a little more of the why here. Why do it this way. I could create another table named “Positions” and link to the “Jobs” and “Tasks” tables. In Positions, I would list the position name, and provide the job ID. And with Tasks, I could have one entry per task item, linked to Position, which would then link to Job. I would also link that Task to the Resume. I could have normalized it even further, but decided against it. Why? Because the data I am displaying revolves around the tasks to build “marketing” platform for me and my experience. It will show prospective employers why I am the person. So I wanted the data to be grouped in this way to provide a list of accomplishments per grouped task. I am sure I could have achieved this with a fully normalized database, but also decided since this was a small application, and I was the only one to be using the backend, and do not expect 10K hits per day on the application, I could do this. For the needs of this application, building the data model this way provides the necessary structure that is needed.

And this section of the data model looks like the following:

Now, we have the data model down, and the basic fields are there. After we do a little baking to create the shell Cake model, controllers, and views, we are ready to start doing some major code work, which will be covered in the next post. And remember, we need to lay this foundation in mind that we are going to create a Facebook application that ties into this resume (and other items). This way it can be shared, can be marketed, and other items can be done using this.