Database design

Principles of database architecture

A database is a collection of data that is organized. A Database Management System (DBMS) is a software application containing the objects that will allow a user to store, organize and retrieve data in any number of ways so that it can be used to run the business.

What this means is that, with the Access 2016 DBMS you create structures called tables
that allow you to store the data so that it’s easy to find later, you create objects called forms that show you how to input the data into the tables and then you create  reports to output selected information from the tables.

For example, if you run a retail operation, you would create a Customers table to store information on the clients, a Products table with the information on what you’re selling and an Invoices table to keep track of who bought what. 

Even before opening the business you would have to list all the products you sell into the Products table using the Products form. Then, when you open an account for a new customer you would use a Customer form to input a customer’s data into the Customers table and an Order form to input the purchase transaction information. Later, you could print any number of Sales statistics reports, grouping and arranging the information from the Invoices, Customers and Products tables to analyze daily or weekly or monthly sales in all kinds of combinations.

Case study: Nick’s Construction Co.
construction worker

Let’s say that Nick runs a small home construction and renovation business. He usually has 5 or 6 projects on the go and 20 or so employees. Obviously, Nick has an accountant who will keep track of the money coming in and going out and will produce reports regularly telling Nick whether he’s making money or not. But the accountant may not be able to provide details on which jobs are costing more than expected or which employees are more productive, etc.

Here’s why Nick needs an Access database to help run his business: he has to keep track of all projects in terms of the number of hours used, the number of employees involved, overtime hours paid, the skills required, the start and end dates of each project, and eventually all the material used, although we’ll get to that part later.

So we’re going to give Nick a database that will have 2 main functions:

  • Manage the manpower on each project so that we know exactly how much each project cost in terms of salaries and conversely where the salary of each employee was used. Nick can then better judge whether his estimates of costs were accurate when he bid on the job and see where the discrepancies occurred in order to correct them.
  • In order to bid on future projects Nick has to take into account the ending date of all on-going projects to figure out who is going to be able to work on the new project, considering the skills needed, the time required and many other factors.

Design is key

The first task in design is to identify entities – individual objects that you have to keep information on. Here we have 2 entities to start with: Employees and Projects.

Next we identify the attributes of each entity. Attributes are fields, characteristics that describe the entity. Some are obvious and others not so much. The analyst has to work with the user to determine what is important.

For Employee: name, address, phone, email, date-of-birth, date-hired, date-terminated, job class. Is it necessary to include Sex (M or F)? Maybe not. But then, maybe Mike would like to bid on a job in Saudi Arabia at a later date and then, that attribute would be necessary. So, when in doubt, include it. As a general rule, it is easier to remove stuff from the database than it is to add it after construction has started.

For Project we would have: title, description, start-date, end-date, budget, leader.

Next we’ll look at relationships between entities.