ksd123
asked on
Advice on Database Design
Hi Experts,
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):Displa
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
Regions(Dropdown):
STATES(Dropdown):
Location/City (Dropdown):
Salary:
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.
1)Regions- RegionId,RegionName
Ex: USA,UK,AUSTRALIA
2)States-StateId,StateName
Ex: FL,CA,MI
3)Locations:LocationId,LocationName
Ex:Lansing,Las Vegas Etc
4)Job Type: JobTypeId,Job TItle
Ex: Perm,contract,Casual
5)Roles: RoleID,RoleName
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
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):Displa
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
Regions(Dropdown):
STATES(Dropdown):
Location/City (Dropdown):
Salary:
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.
1)Regions- RegionId,RegionName
Ex: USA,UK,AUSTRALIA
2)States-StateId,StateName
Ex: FL,CA,MI
3)Locations:LocationId,LocationName
Ex:Lansing,Las Vegas Etc
4)Job Type: JobTypeId,Job TItle
Ex: Perm,contract,Casual
5)Roles: RoleID,RoleName
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
Hi,
Consider using the state abbreviation as the key - which means for most queries one less table to join to for the select. Its not as if the abbreviation or natural key is going to get changed.
Think about having a free text salary field, but link to a salary table which has bands every 10k or so - easier for searching.
Regards
David
Consider using the state abbreviation as the key - which means for most queries one less table to join to for the select. Its not as if the abbreviation or natural key is going to get changed.
Think about having a free text salary field, but link to a salary table which has bands every 10k or so - easier for searching.
Regards
David
ASKER
Hi,
Thanks for your comments.But I need briefly explaination for the following questions that would really help me to understand basic db design concepts.
1)How to handle salary data?plz suggest me the best way with table structure?
2)In all the 5 tables , which columns fit for Foreign Key and also Relationships between the tables?(one-many/many-one etc).
3)Since we just had 2 columns in all the tables do I still need to do normalization?
Tables:
1)Regions:-RegionId (PK), Region Name
2)States:-StateId (PK), State Name
3)Locations:-LocationId(PK
4)Jobs :-JobTypeId(PK), Job TItle
5)Roles:-RoleID(PK), Role Name
Thanks for your comments.But I need briefly explaination for the following questions that would really help me to understand basic db design concepts.
1)How to handle salary data?plz suggest me the best way with table structure?
2)In all the 5 tables , which columns fit for Foreign Key and also Relationships between the tables?(one-many/many-one etc).
3)Since we just had 2 columns in all the tables do I still need to do normalization?
Tables:
1)Regions:-RegionId (PK), Region Name
2)States:-StateId (PK), State Name
3)Locations:-LocationId(PK
4)Jobs :-JobTypeId(PK), Job TItle
5)Roles:-RoleID(PK), Role Name
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi ScottPletcher,
Thank you so much for your valuable inputs and now got some basic understanding of DB design.Based on my requirement will create some Stored procs,Views,Functions on these tables.
But I have little confusion on how to maintain Audit/Log details in genearal and in my case on which table do I need create trigger ?
The other question I have is ,by default cluster index will create on PK and in my case when should I go for non-clustered index?
Thanks in Advance
Thank you so much for your valuable inputs and now got some basic understanding of DB design.Based on my requirement will create some Stored procs,Views,Functions on these tables.
But I have little confusion on how to maintain Audit/Log details in genearal and in my case on which table do I need create trigger ?
The other question I have is ,by default cluster index will create on PK and in my case when should I go for non-clustered index?
Thanks in Advance
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks ScottPletcher
You will need a main database that links all of the information for an individual position. This can then either have the salary field as a string, or as an index to the salary table.
eg
Table AvailableJobs
JobID as integer
Region as integer
State as integer
Location as integer
JobType as integer
Role as integer
Salary as Integer or String
This design is using normalization, the ID fields of the individuakl tables should be the primary key for thiose tables