Database Schema for Resume/CV and Separate Sections

Question

I am trying to figure out if I am on the right track here.

resumes
id
user_id

resume_sections
id
name
desc

Sections are those that we normally find in resumes, experience, skills, education and so one.

Thanks in advanced!

Answer 1

You are on the right track here. Somethings to consider:

Users

Likely a User can have multiple resumes. Job candidates often tailor a resume to a job opening and want to keep all of those. This means you will need a User table. Having a user table also gives you a place to store attributes of the user such as their name.

Sections

A resume has lots of kinds of sections, such as experience, job history, education, and so on. This means you will want to make you resume sections table a Section Type table.

Thing Types vs. Things

This brings up a common pattern in logical database schema design - the differentiation between kinds of things and the things themselves. I would recommend you add a Section table related to Resume that holds the actual section of an instance of a resume for a user.

Model Example

Oracle provides a free tool called Oracle Data Modeler which you can use to create a visual model of the table, called an Entity Relationship Diagram (ERD), and then generate your DDL from it. Here is an example starter model of your database design: Creating an ERD also helps you start to clarify the requirements. For example, I made the key to the section table the resume and a sequence number. This means you could have 2 or more sections on a resume with the same type. Does this make sense? Or does it make sense to say there can only be one section of each type per resume? Second, you want to think about what uniquely identifies a row in each table. For example, I have used a Resume Number for the key to the resume table. Wouldn't it make sense to say that each row would be uniquely identified by the User Name and the resume name? This would prevent the storage of duplicate resumes.

DDL Generation

Once you nail down all the business rules, you can use Oracle Data Modeler to generate your DDL. While it doesn't support mySQL, you can still generate it and get the base structure and then correct for mySQL specific syntax. Here is a sample of what I quickly generated from this model:

-- Generated by Oracle SQL Developer Data Modeler 4.1.0.873
--   at:        2015-08-19 09:15:38 EDT
--   site:      Oracle Database 12c
--   type:      Oracle Database 12c

CREATE TABLE "Resume"
  (
    "Resume_Number" NUMBER (10) NOT NULL ,
    "User_Id"       NUMBER (10) NOT NULL ,
    "Name"          VARCHAR2 (100) NOT NULL ,
    "Description"   VARCHAR2 (1000) NOT NULL
  ) ;
ALTER TABLE "Resume" ADD CONSTRAINT "Resume_PK" PRIMARY KEY ( "Resume_Number" )
;

CREATE TABLE "Section"
  (
    "Resume_Number"           NUMBER (10) NOT NULL ,
    "Section_Sequence_Number" NUMBER (5) NOT NULL ,
    "Section_Type_Id"         NUMBER (5) NOT NULL ,
    "Content"                 VARCHAR2 (4000) NOT NULL
  ) ;
ALTER TABLE "Section" ADD CONSTRAINT "Section_PK" 
PRIMARY KEY ( "Resume_Number", "Section_Sequence_Number" ) ;

CREATE TABLE "Section_Type"
  (
    "Section_Type_Id" NUMBER (5) NOT NULL ,
    "Name"            VARCHAR2 (100) NOT NULL ,
    "Description"     VARCHAR2 (1000) NOT NULL ,
    "Order_Number"    NUMBER (5) NOT NULL
  ) ;
ALTER TABLE "Section_Type" ADD CONSTRAINT "Section_Type_PK" 
PRIMARY KEY ( "Section_Type_Id" ) ;

CREATE TABLE "User"
  (
    "User_Id" NUMBER (10) NOT NULL ,
    "Name"    VARCHAR2 (100) NOT NULL
  ) ;
ALTER TABLE "User" ADD CONSTRAINT "User_PK" 
PRIMARY KEY ( "User_Id" ) ;

ALTER TABLE "Resume" ADD CONSTRAINT "Resume_User_FK" 
FOREIGN KEY ( "User_Id" )
REFERENCES "User" ( "User_Id" ) ;

ALTER TABLE "Section" ADD CONSTRAINT "Section_Resume_FK" 
FOREIGN KEY ( "Resume_Number" ) 
REFERENCES "Resume" ( "Resume_Number" ) ;

ALTER TABLE "Section" ADD CONSTRAINT "Section_Section_Type_FK" 
FOREIGN KEY ( "Section_Type_Id" ) 
REFERENCES "Section_Type" ( "Section_Type_Id" ) ;

This gives you a good deal of DDL to start with. A good reference on data modeling is David Hay's Enterprise Model Patterns. I hope this answer helps you move forward with completing your design!