最佳数据库存储方法

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 ?

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.

Definitely go with option 1. Good database design is vital, and good database design involves normalizing your data which is only possible if you model distinct entities with their own tables.

Take a look at this article on database normalization, which has a good discussion of the concepts involved.

Option 2 is clearly and unequivocally wrong. If you're not going to decompose your data into rows and columns and follow at least the first couple of rules of data normalization then there's no reason to use a relational database (you could use XML documents in file system folders).

Option 1 is probably most correct. In the event that all elements are expected to be present in all resumes, and no elements repeat within a resume, then you could actually have one table for the entire resume, with each field in its own column. Since I don't imagine that those conditions are met (work history and education should probably be repeatable elements, for instance) then separate tables per element is the correct choice.