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!
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this communityYou are on the right track here. Somethings to consider:
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.
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.
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.
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.
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!