2

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!

5
+50

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: Example Data Model 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!

| improve this answer | |
  • Hi @ToddEverett, I am curious to what the column "Order_Number" NUMBER (5) NOT NULL in the table Section_Type means? Will it hold the order numbers of each section? – John Smith Jun 4 '17 at 11:22
  • 1
    The idea there was to show that a given resume section type - say work history or education - would have an order in which it is to be included in the resume - say first, or second, etc. In this example, the business rule being implemented is that no matter how many different resume's are created, the sections are always listed in the same order. This is another example of how the ER Diagram implies business rules that must be validated. Perhaps it is the case that each section type could have a different order on the individual resumes and if so a design change is in order. – Todd Everett Jun 5 '17 at 14:55
  • So it doesn't hold the sub-meta blocks, and then decide the order of these within a section? E.g. Experience is one section, but under experience you have items related to experience that hold information about work-places etc. And that you should keep an order of these? Correct me if I am wrong. Because the way I see it, I think order_number should also be put on "resume" table, to keep track of section order, first to last, right. Think reordering etc. – John Smith Jun 6 '17 at 7:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.