Personnel - Employee Entity, Data Types

In this page we will look at creating the basic (concrete) entity Employee as it can exist without referencing other entities (contains no FKs).

Logical Design

The Second phase in design is to take the conceptual design and produce a logical design. The Logical design phase "Produces a detailed design that converts the users requirements into formal structures, rules and relationships."

Data dictionary

A Data dictionary is a tool used in the Logical Design phase and "describes the TYPE of data you want to store about your entity".

Naming conventions

In order to provide consistency, you will find professional programming businesses will have standards that you are expected to use. In this tutorial some commonly used conventions will be introduced that should add consistency to your work.

• Objects: Database objects like the database, or entities (tables) will have a three character (lowercase) prefix.

• Database = db ( e.g. dbPersonnel.accdb)

• Entity = tbl (e.g., tblEmployee, tblClient)

Attributes are contained in a table so have a prefix that identifies the primary table they belong to (they keep the same name if they are a foreign key in another table). Three ( 3) Characters is usually enough, and as they belong to an Entity Object start with a Capital Letter (e.g., Emp).

e.g., EmpLastName = Employee Last Name

CamelCase

Using spaces in Names of objects is discouraged (e.g., Emp Last Name), as these can cause issues when writing programming statements (like SQL, HTML, JavaScript, etc.). It is better to use a format known as CamelCase, where words are joined with a Capital Letter to provide separation (e.g., EmpLastName).

Something you may come across is the use of underscore between words (e.g., Emp_Last_Name). This comes from the very early days of programming , particularly when programming in a language called COBOL (1960s) and when Printers could only print UPPERCASE letters (e.g. EMP_LAST_NAME) and was a great way to name objects.

Primary keys

If a Primary Key is created using an autonumber in a table, using ID (Identifier) is good practice, e.g., EmpID, ClientID, AssetID, etc.

Employee: Data Dictionary

For an entity we need a way to describe the TYPE of data each attribute holds. For this we develop a Data Dictionary.

Note that the Data Dictionary does NOT contain what the data is (e.g., any actual names).

Candidate Key

In an entity there can be many attributes that could uniquely identify the record. These are referred to as Candidate keys. For an Employee some attributes that could uniquely identify a person could be Inland Revenue Department (IRD)Number, Mobile phone Number, National Health Identifier (NHI) Number, or a combination of two attributes LastName+FirstName.

Primary Keys (PK)

We need to have a consistent and reliable way to access each record (instance) uniquely. This is achieved by selecting a Primary Key from possible Candidate Keys.

Rules for a primary key include

• Must be unique

• Must exist for each record

• Never changes

• Is created at the time the instance is created

When selecting a PK you need to be careful that

• The data for the PK is available (This may not be the case when entering IRD-Number, NHI Number)

• The data will always exist when the record is created. (Not everyone has a mobile phone)

• The data is from another database and there are Privacy implications regarding Data matching, e.g., IRD-Number, NHI Number

• It is possible when the record is created the data is not available., e.g., a person does not necessarily carry/know their IRD-Number.

Composite Key

A Primary key could be made up of two attributes (so long as together they follow the Primary Key rules). So, LastName+FirstName could be a Primary Key but there several issues.

• There are many people with the same name.

• There are people with only one name.

• A person can change their name (e.g., when they get married).

So this combination should not be used as a Primary Key.

For our example previously, the only reasonable Primary key for the Employee entity would be EmpID

Entity Diagram

For the Employee Entity we can show it in an attributed entity diagram.

References

Microsoft Support (2022). Data types for Access desktop databases. https://support.microsoft.com/en-us/office/data-types-for-access-desktop-databases-df2b83ba-cef6-436d-b679-3418f622e482