CODEWITHSUNDEEP
phpmysqldatabaserelational-database
Tarun
Tarun

Reputation: 3165

Database design for storing job seeker information

I want to store following information about the job seeker in mysql tables .

Personal Information
1)Name
2)Caste
3)Date of Birth
4)Annual Income (optional)
5)Source of Income (Optional)
6)Identification Mark

Family Information
1)Father Name
2)Mother Name
3)Father's source of Income
4)Mother's source of Income

Contact Information
1)Address
2)Pin Code
3)District
4)State
5)Mobile Number
6)Name of person who own a mobile(If mobile belong to someone-else)
7)Relation with person who own a mobile(If mobile belong to someone-else)

Education Qualification
1)Metric - Subject, Total Marks , obtained Marks , Year of Passing , Board Name , University Name.
2)Intermidiate(optional) - Subject, Total Marks , obtained Marks , Year of Passing , Board Name , University Name.
3)Graduation (optional) - Subject, Total Marks , obtained Marks , Year of Passing , Board Name , University Name.

Yes/No Questions There are around 10 Yes/No Types Questions .

After recieving user application for a particular post . The user application would go through following screening process .

1)Short list candidates who have submitted hard copy of their application.
2)Screen candidate based on information(education qualification,annual income etc) submit by them and shortlist candidate for first merit list.
3)Some other screen process to shortlist candidate for final merit list.

So I would be required to store following information about status of application :

1)Hard Copy Received Or Not and if received then i also need to store the date when the hard copy was received
2)Shortlisted in First Merit List or not. If shortlisted then i also need to store the rank in first merit list and if not shortlisted then i need to store the reason why the particular application was rejected . 3)Shortlisted in final merit list or not If shortlisted then i also need to store the rank of candidate in final merit list and if not shortlisted then i need to store the reason why the particular application was rejected.

A user would have single set of personal , contact, family ,education information . The problem that i see with single table is with optional fields . Can you please suggest me a better schema .

Upvotes: 1

Views: 470

Answers (1)

Laura Ritchey
Laura Ritchey

Reputation: 711

No you should not. There are many reasons why a single table is not appropriate.

Some questions to ask:

Is there a possibility that a user may have more than one address or phone? Could more than one person belong to the same University? Is it likely that a subject will be used more than once between all the people in your database?

Generally speaking, if you have sections like you listed, they would likely be good candidates for separate tables. If you have any reusable data, it should be separated into multiple tables.

Please consider checking out some tutorials on normalization.

In the meantime, here are a few basic rules of good database design:

Rule 1: Eliminate Repeating Groups. Make a separate table for each set of related attributes, and give each table a primary key.

Rule 2: Eliminate Redundant Data, if an attribute depends on only part of a multi-valued key, remove it to a separate table.

Rule 3: Eliminate columns not dependent on key. If attributes do not contribute to a description of the key, remove them to a separate table.

Check out this URL for more information: http://www.dbnormalization.com/

Upvotes: 1

Related Questions