CODEWITHSUNDEEP
sqldatabase
aman girma
aman girma

Reputation: 684

How Should the following table be Normalized?

I have a table called Employee with ID,Fname,Lname,dateEmployed,Program, fieldOfStudy,Salary,jobTitle and JobDescription columns. I need to Normalize it but i am not sure how to do that. So how should i normalize it?

This is what i have tried

I created two tables Employee and Job as shown below and referenced the jobId column from Job table in Employee table.

here is Employee Table

CREATE TABLE EMPLOYEE(
    Id int primary key identity,
    FName nvarchar(50),
    LName nvarchar(50),
    dateEmployed Date,
    jobID int foreign key references job(jbId)
);

here is the Job Table

CREATE TABLE Job(
    jobId int primary key identity,
    Title nvarchar(200),
    program nvarchar(30),
    salary float,
    fieldOfStudy nvarchar(50)
    JobDescription nvarchar(max)
);

My Question is just about Normalization not on sql query

Upvotes: 1

Views: 143

Answers (1)

GMB
GMB

Reputation: 222432

Your question actually breaks down to : what is the relationship between jobs and employees (which you did not thoroughly explained). Possible cases are :

  • 1-N : if each employee has one job and several employees can have the same job, the your setup with two tables and a foreign key in employee towards job is fine

  • 1-1 : if each employee has one job, and each job belongs to a single employee, then you do not need two tables : you can just stuff everything in the employee table

  • N-N : if an employee can have several jobs, and each job can belong to several employees, then you would need to create a third table, a bridge table to represent that relationship, where each row would store one foreign key towards the jobs table and another towards the employees table

Upvotes: 1

Related Questions