Search code examples
phpmysqldatabasenormalizationorganization

Best Database Storage Method

My software (written in PHP) allows users to use an HTML form to enter information for their 'Resume'. There are several different elements of this 'resume' including the user's education, work history, awards, cover letter, and etc. Each element is unique in the data it contains. For example, education requires fields such as graduation year, school name, degree, etc.

My question is, what would be the best approach to store the resume data in a mySQL database? My current choices are:

  • Create individual tables for each resume element (resume_education, resume_awards, etc.)
  • Use a single table for all elements of the resume and insert the element data in the form of multi-dimensional arrays. (IE: one table that holds resume data with columns such as education, workhistory, blah blah. Data in education or similar field would be compiled into an array such as ("grad_year"=>"1990", "school_name"=>"Cool School") or something similar where the usage of indicating characters denotes a new field).

The first choice offers an ease of maintenance and coding whereas the second choice would seem to offer a substantial decrease in backup and maybe load times, and database maintenance. Maybe there is an altogether better approach? What would you recommend ?

Solution

  • I'd go for multiple tables, but not named tables something more generic.

    Eg. tables

    resume_section
id,name
1,personal details
2,education
3,work history

resume_section_attributes
id,resume_section_id,name
1,1,name
2,1,phone
3,1,email
4,2,school_name
5,2,grad_year
6,3,company_name
7,3,number_of_years

user_resume
id,user_id,resume_section_attribute_id,value
1,99,1,My Name
2,99,2,12345678

    Now when you want to build a users resume you can go straight to the user resume table and get all their details and join with the attributes table for the attribute names etc. You could change the order of attrs for display, easily add new ones. Set them as required or not. Set limits for the number of phone numbers or something. The possibilities for extending are endless.