CODEWITHSUNDEEP
mysqlsqldatabase
Cappy
Cappy

Reputation: 15

Database Architecture (Help)

Am i doing this correctly? I know it's super simply, but I am just now getting some DBA experience.

I have an Entity Table that is non static called Emps. The Emps table stores all the Employee personal information like phone numbers and so on. I then have a table called EmployeeOfflineTraining. This Table stores all Employees who are certified in Classroom training like Forklift.

Do i simply put a foreign key inside the EmployeeOfflineTraining table to assign training records to that employee ID?

Emps (Phone, Agency, Name)

EmployeeOffLineTraining (GpcScore, Forklift Score, ForklifeCertified, EmpsId)

Upvotes: 0

Views: 47

Answers (2)

Explosion Pills
Explosion Pills

Reputation: 191729

I would create an indirection table that maps TrainingId to EmployeeOfflineTraining as well. This gives you flexibility in case you need to introduce multiple training types -- you can simply add a row to the Training table rather than having to alter the EmployeeOFflineTraining table.

You may also consider creating an Agency and EmployeeAgency table as well in the same vein.

CREATE TABLE Emps (
  empId int unsigned not null auto_increment primary key,
  phone varchar(255),
  agency varchar(255),
  name varchar(255)
);
CREATE TABLE Training (
  trainingId int unsigned not null auto_increment primary key,
  name varchar(255)
);
CREATE TABLE EmployeeOfflineTraining (
  eotId int unsigned not null auto_increment primary key,
  empId int unsigned not null,
  trainingId int unsigned not null,
  score smallint unsigned,
  certifiedDate datetime,
  foreign key (empId) references Emps(empId),
  foreign key (trainingId) references Training(trainingId)
);

Upvotes: 1

gh9
gh9

Reputation: 10703

Yes.Wikipedia Entry On DB Normilzation What you are looking for is called Databse Normilzation

Employees

EmployeeID PrimaryKey

Phone

EmployeeName

AgencyId

Agencies

AgencyId PrimaryKey

AgencyName

EmployeeOfflineTraining

EMployeeOfflineTrainingId PrimaryKey

EmployeeId ForiegnKey to Employee Table

GpcSocre

ForkliftScore

ForkliftCertified

Edit As pointed out be Sean Lange The OfflineTraining Table has major problems which are not addressed in this answer.

Upvotes: 0

Related Questions