Three tables for employees

As preparation for a few examples we are about to explore in the Qt database support area, I had to create three MySql tables that could be worthy to examine with a certain details.

The idea is that we are storing data for managing employees' information. We just need to keep track of the guy name, extension phone number, email, start working date, and department id.

The department id would be the foreign key that gives us access to the department table, where, besides its id, a couple of other information are stored for the department, that is its name and the location id.

This location id would be, again, a foreign key to another table, the location one, where we are about to store the id and the name od the location.

This structure will allow us to manage the employees of a firm having a few departments in different locations.

Let's see how I implemented that for MySql.

The location table is quite simple:

CREATE TABLE test.location
(
 id INTEGER PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(40) NOT NULL UNIQUE
);

The location should be specified and can't be duplicated, the id has not to be necessary provided by the user, if not specified would be MySql to take care to generate a valid value for it, by auto incrementation.

A bit more interesting the department table:

CREATE TABLE test.department 
(
  id            INTEGER PRIMARY KEY AUTO_INCREMENT,
  name          VARCHAR(40) NOT NULL UNIQUE,
  location_id   INTEGER NOT NULL,
  CONSTRAINT fk_loc_id FOREIGN KEY(location_id) REFERENCES test.location (id)
);

The point of interest here is that the location id has a constraint, a foreign key that estabilish a connection to the location id. That means that the department location_id value should match the value of an existing location id.

The other way round, the fact that location id is a foreign key for the department location_id implies that we can't change it if this would result in a broken connection between tables.

The employee table has a bit more meat, but there is not much to say more about it:

CREATE TABLE test.employee 
(
  id              INTEGER PRIMARY KEY AUTO_INCREMENT,
  name            VARCHAR(40) NOT NULL,
  department_id   INTEGER NOT NULL,
  extension       INTEGER NOT NULL,
  email           VARCHAR(40) NOT NULL UNIQUE,
  startdate       DATE NOT NULL,
  CONSTRAINT fk_dep_id FOREIGN KEY(department_id) REFERENCES test.department (id)
);

Here is department_id that is connected to the id of the department table by a foreign key relation. It is assumed that could exist more than an employee with the same name, but in any case the email should be unique.

I wrote this post while reading "C++ GUI Programming with Qt 4, Second Edition" by Jasmin Blanchette and Mark Summerfield. They create their tables for SQLite 3 that uses a SQL dialect that has a few difference with the one used by MySql 5.