So, what do March Madness, Fantasy Football, Facebook and most businesses have in common? They all utilize databases to keep track of data and provide answers to our most compelling questions. How did the NCAA calculate the ratings percentage index (RPI)? How many points did your fantasy team score and was it more than the other team? How do you find long-lost friends on Facebook? Other examples of databases include Outlook contacts, music files on your iPod, QuickBooks accounting software and the bank records of your checking account.
Businesses use databases to:
- Determine if they made a profit this week, this month and this year and compare the findings to previous weeks, months and years;
- Determine if their sales teams met their goals and calculate their proper commissions;
- Analyze employee individual performance this year versus last year in sales, job performance and productivity;
- Develop sales and marketing campaigns; and
- Develop job estimation and quotes.
Databases allow businesses to quickly accomplish these tasks. A properly designed database with ease of access for users will provide a means of data entry, data querying and data reporting. This topic may not be very exciting, but databases can provide great productivity improvements and tools to help your business succeed.
What do you need to develop a database?
- A plan regarding the data you want to keep and an understanding of how the different data relates to each other;
- A place to store the database and the data;
- A method allowing users to access the data (a user interface);
- A database administrator to design and maintain the database;
- A person who will be able to view, add, update and/or delete data (permissions); and
- A method of communication to request changes to the database.
What kind of data will you be tracking?
An outline of the data that will be tracked must be prepared. This may include customer records, employee files, product inventory, sales data, accounting data and job details. There must also be a determination regarding what makes each type of data unique, such as a Social Security number for an employee, an invoice number for an invoice, or a part number for an inventory record. In some cases, multiple unique fields exist for a data record, such as the invoice number, transaction date and transaction type to store records concerning invoice transactions that have multiple payments for the same invoice number.
Where should a database be stored?
If it is a simple database for one user, it could be located on one’s personal computer/laptop. If it is a business that shares the database with multiple users, then it should be stored on a server on the network or Internet.
In order to create a database on a server, the server must have the appropriate software program installed. Most small businesses use Microsoft SQL Server, which can be costly, but Microsoft SQL Server Express and MySQL are free applications that can be downloaded from the Internet.
If the database is for a single user, the Microsoft Office suite includes Microsoft Access, which can be used to store and access a simple database that will not exceed the specified data storage space (2G).
How is a database designed?
A database consists of:
- Data structure to store the data;
- Data queries to access data on specific parameters;
- A user interface to enter data; and
- Data reports to print information.
Data structure
The structure consists of “tables” that have field names, data types and data lengths. Each table has designated unique fields called primary keys. Tables are related to each other by specific designated common fields. (See the Data Structure Chart on the next page for an example of tables to track employee files.)
The unique fields in the tables include Employees: Social Security #; Employee Moves: Social Security and Date Started; and Job Titles: Job Title. The related fields are Social Security # for Employee and Employee Moves and Job Title for Employee Moves and Job Titles. These related fields are considered “joins” and enable the database designer to query the database. By using these joins, this database can now be queried to show all employees with the last name “Smith,” associated job moves and their minimum pay rates for each job.
The GUI
How is it that we don’t see the tables when accessing our companies’ databases? The database design team must create a graphical user interface (GUI) that allows users to see the data, enter new data, change data and occasionally delete data. This interface can be developed using an application like Microsoft Visual Studio, Microsoft Access Projects or even a Web site using HTML/ PHP. This software will enable the designer to create “forms” to enter data and “reports” to generate reports.
Forms and reports
Forms and reports are generated through the use of queries that access data using specific parameters. If the request is logical, then the data can be queried. The key lies in understanding how the data tables are related and the specific parameters required. (A database designer who understands how to query the data is necessary.) The data can also be queried with counts, sums and comparisons such as greater than, less than, equal to or the like.
What’s next?
A database administrator must be designated to design, maintain and develop the database further. If there is one thing that is a given regarding a properly designed database, it is that everyone will want more. For example:
- The owner will want more reports;
- The users will want to track more data;
- The users will want data updated automatically;
- Design “bugs” will need to be fixed; and
- Permissions will need to be created or changed.
Each database can be configured to give specific users certain permissions to view data, enter data and even delete data. This should be handled by the database administrator, per company policy.
Data entry and reports are only the tip of the iceberg when it comes to what databases can do. Data can be automatically updated; databases can communicate with other databases; spreadsheet files can be imported; data can be exported; and filters can be created to generate reports more easily. A database administrator with programming skills is required to make all this happen.
A common issue surrounding database design is developing an interface that significantly reduces the chances that users will make data entry mistakes. We may not want a user to enter a sale date of 1/10/1910, enter a state code of “WW” or complete a data entry form that is missing vital information. This is done using triggers, constraints, proper data types and error handling.
Finally, as databases grow, users must communicate to database administrators when there are problems or when changes in design are needed. Users should be prepared to detail exactly what is needed, as database administrators can only design in specifics. Users must explain exactly where the issue exists, since database administrators rarely use the database like users do. Database administrators design a form, then move on to designing another form and, many times, never use that form again.
Can a database do everything?
No. It cannot:
- Manage your employees;
- Prevent all data entry errors;
- Create sales; or
- Make your dinner, although it could randomly select what you should make.
What interesting tasks have been accomplished using databases?
I have developed a corporate database to perform “metrics assigning.” When field personnel are assigned to specific clients, I utilize a metrics analysis to determine which employee is best suited to help that particular client.
I have developed “data scrubbing,” which allows inventory files (more than three million records) to be updated using at least four different suppliers, while minimizing duplication of companies in the corporate database. This function also analyzes the types of clients that achieve the most success after using our services. These leads, which are based upon desired parameters and geographical regions, are then distributed to our corporate sales group to maximize productivity.
I have developed complex payroll systems for multiple departments to generate payrolls quickly and accurately.
I have developed an application that can be used by field personnel via the Internet to access our corporate database and view current job assignments.
Finally, on a personal note, I have designed an accounting software database and a planner/contacts database for my wife. In fact, I even developed a small database to track my golf game and analyze my areas of needed improvement. Unfortunately, my golf game has yet to improve.
