[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Advice on Database Design

Posted on 2013-05-25
8
Medium Priority
?
297 Views
Last Modified: 2013-06-02
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):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

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
0
Comment
Question by:ksd123
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 15

Expert Comment

by:ChloesDad
ID: 39196842
As far as salaty goes, you can do it several ways. Firstly have a string so that you have complete control over what goes in there. You could also have a table that has salary ranges.

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
0
 
LVL 35

Expert Comment

by:David Todd
ID: 39197409
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
0
 

Author Comment

by:ksd123
ID: 39198861
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), Location Name

4)Jobs :-JobTypeId(PK), Job TItle

5)Roles:-RoleID(PK), Role Name
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 15

Assisted Solution

by:ChloesDad
ChloesDad earned 200 total points
ID: 39198873
Normalisation is a yes, as it means that you only store an integer in the main table rather than having a full string each time.

With the salary, it depends on what you want to save. As most jobs will offer a salary range then you could use two numbers SalaryMin and Salary Max. You can then use a dropdown on the search page with salary ranges .

The references to the ID fields in the lookup tables will all be foreign keys as they reference a primary key in another table. For this reason, the relationship between the two tables is called a one to many relationship between the referenced table and the referencing table. (i.e. one record in the lookup table is referenced many times in the main table)
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 1400 total points
ID: 39201989
Spend some time on this design!  Proper db design, including normalization, are absolutely necessary to end up with a good app.

See initial, quick sample table designs below for some ideas.  "Subregion" seems a little awkward, maybe you can think of a better generic term.

[Btw, never use a string to hold salary and/or salary range or any other genuinely numeric data.  Instead, use separate string columns or codes as appropriate to further define the salary.]



Regions
    RegionId
    Name
    Subregion_Name /* 'State'(USA)|'State/Territory'(Australia)|'??'(UK/GB) */

   
Subregions
    SubregionId /* 'CA'|'FL'|'??'|... */
    RegionId; FK /* 'USA'|'Aus';... */
    Name /* 'California'|'Florida'|'Queensland'(?)|... */


Locations
    LocationId
    Name
    SubregionId; FK


JobTypes
    JobTypeId
    [Type] /* 'Casual'(?)|'Contract'|'Permanent'|... */

JobCategories
    JobCategoryId
    Category /* 'Full-time'|'Part-time'|...*/

JobShifts
    JobShiftId
    Shift /* '8AM-5PM'|'Night shift'|'Swing shift'|...*/

JobLevels
    JobLevelId
    Level /* 'Executive'|'Managerial'|'Team Lead'|'Vice President'|... */
   
   
JobSalaries
    JobSalaryId
    Classification /* 'Hourly'|'Salaried'|... */
    SalaryTimePeriod /* 'Hourly'|'Per piece produced'|'Yearly'|... */
    MinSalary decimal(9, 2)
    MaxSalary decimal(9, 2)


Jobs
    JobId
    JobTypeId; FK
    JobCategoryId; FK
    JobShiftId; FK
    JobLevelId; FK
    JobSalaryId; FK
    LocationId; FK
    Description /* 'Manager of ...'|'Team lead for...'|'Will assist...'|... */
0
 

Author Comment

by:ksd123
ID: 39203225
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
0
 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 1400 total points
ID: 39204814
Again, do NOT rush into creating procs, functions, etc., first spend some time on just the db design.  That small investment in time will pay off huge later.

I'm not sure what or why you need to audit/log data.  If you have Enterprise edition, you can use CDC.  Otherwise, yes, you may have to use triggers.

As to clustering, you should cluster on a column you will often sort and/or group on.  You'll need to further review your requirements.  At first, you can just make your best guess on which should be the clus index, make the rest nonclus, then after the system is running for a while look at missing index stats (sys.dm_db_missing_index_* views) and index usage stats (sys.dm_db_index_usage_stats).  Unless you have millions of rows at the start, you can get away with a best guess and adjust it later.
0
 

Author Closing Comment

by:ksd123
ID: 39214195
Thanks ScottPletcher
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question