Adding a table

The database doesn’t currently hold any information about employees.

A new employee table would allow you to store information about who they are, along with their contact details and contract information.

When adding a new table, you should first consider what information needs to be stored.

The basic information about an employee might be:

• Name
• Address
• Phone number
• Email address
• How many days a week they work

You can then break this down into a set of fields and associated data types:

• first_name (text)
• surname (text)
• address_number (integer)
• address_1 (text)
• address_2 (text)
• locality (text)
• region (text)
• postal_code (text)
• phone number (text)
• days_per_week (real)

You will notice that I have used multiple fields to store name and address by splitting them into their component parts. For example, name is split into first_name and surname, while address is broken down into:

• address_number (the number of the property)
• address_1 (the first line of the address, probably the street)
• address_2 (an optional second line of the address, maybe for a village or town)
• locality (probably the city)
• region (the county or state)
• postal_code (the postcode of the address)

Breaking the information down into multiple fields means that it can be searched and analysed easily. For example, you can filter employees by city. Having a single field for address would make this more difficult and less reliable.

In addition to the data, an employee_id primary key should also be included that meets the following criteria:

• It’s unique: no two records can have the same primary key data
• The primary key value should never be reused; if a record is deleted from the table, the primary key value should not be allocated to a new record
• It must not be modified
• There must be no NULL values in the primary key

Creating the table

1. Execute the following SQL statement to create the employee table with these fields:

   CREATE TABLE employee (
    employee_id text PRIMARY KEY,
    first_name text,
    surname text,
    address_number integer,
    address_1 text,
    address_2 text,
    locality text,
    region text,
    postal_code text,
    phone_number text,
    days_per_week real
    );
   

   The CREATE TABLE statement is used to define the table name followed by the field name and the data type.

   The PRIMARY KEY parameter is added to the employee_id field to denote that this is the primary key for the table.

2. Write an INSERT SQL statement to create an employee in the table, and then execute it.

   The syntax for an INSERT statement is:

   INSERT INTO table_name (field1_name, field2_name, field3_name, ...) VALUES (field1_value, field2_value, field3_value, ...);
   

You can download an example database that includes the changes to the sales table and the new employee database.

What other information do you think you would need to store about your employees? What fields would you add to the employee table? What data types would you use? Share your thoughts in the comments section.