Split and create relations
Instead, we want to keep independent entities separate
and create relations between them. This process is called normalisation.
Using the principles of relational databases, the Employee and Department data can be
restructured into two separate tables (say EMPLOYEE and DEPARTMENT), as shown in Figures 5 to 8.
We now have created the relation between the tables, having the department
data independent to the employee data, and associating each employee to a specific department.
From Figures 6 and 7, we can see the relationship between DEPARTMENT and EMPLOYEE
is built on the attribute departmentNo. This was made possible by setting up the
"Foreign Key" during the creation of the table Employee.
Therefore, when we will want to insert a new employee along with his/her department,
we will get an error if we do not provide a valid departmentNo.
In other word, the insertion won't happen if we want to assign a department that doesn't exist.
OPTIONAL: For tips and rules on how to normalise your database properly, have a look at this link.
I didn't use any PRIMARY KEY
in the examples here. Using one is good practice. Your tables will be more consistent
and it will accelerate your queries.
As w3schools puts it:
"The PRIMARY KEY constraint uniquely identifies each record in a database table.
Primary keys must contain UNIQUE values.
A primary key column cannot contain NULL values.
Most tables should have a primary key, and each table can have only ONE primary key."
You can set your primary key using the "PK" tick box (CP in my french example)
for the ID column (e.g. employeeNo).
We could have separated first name and last name to have more "atomic" data.