Advice on Database Design
Posted on 2013-05-25
I am planning to develop a small careers web site with the following 2 pages in Asp.net and need advice on DB design.
1)Display Jobs Page
Regions(Dropdown): Showing All Regions (USA,UK,AUSTRALIA ETC)
States(Dropdown): Displaying STATES Corresponding to Region.
Locations(Dropdown):Displaying cities based on State.
A Grid displaying all jobs with below fields
Job Title Date Work Type Location/City Salary
2)Post Jobs Page
Job Type (Dropdown):To display all Job Types
Job Title (Dropdown):To display all Job Roles
Description: Desc of the Job
For the above task I am considering following Tables and fields in my database and need advice on database design and relationships on the tables.
Ex:Lansing,Las Vegas Etc
4)Job Type: JobTypeId,Job TItle
Ex:Project Manager,Team Lead etc.
1)I have Salary column and not sure this should have a separate table or Do I need to include Salary column in any of the above tables?
2)Do I need more tables apart from above 5 tables to acheive my task? If yes, let me know the table(s) with field names.
And also let me know if additional fields are required for the above 5 tables.
3)How to idenfiy Primary key's, Foreign keys and relationships on these tables.
4)For this simple database with just having 5 or 6 tables, Do I need Normalization?
I do understand DB design is a heavy subject and your inputs would be highly appreciateted