Relational databases are widely used in corporate America. They track items such as inventory, sales data, parts lists, music collections, membership lists, medical histories, etc. On the Internet, databases are used to manage e-mail lists, keep online banking records, and even manage content for Web pages. Databases can provide valuable insight for roofing contractors as well. Once you become familiar with what a relational database is, and how a Relational Database Management System (RDMS) works, you may begin to visualize applications for your business.
A RDMS stores information about a business in table format. Each table stores information about a single subject, such as “employees.” Another table might store information about “projects.” Still another table might keep records about “customers.” Sitting down and developing these tables is called “data modeling” and is the first step in building a database.
Storing data in separate tables, separate by subject, greatly reduces the amount of data we need to store and manipulate. For example, a project table would list projects including the customer’s name and address. But if you have multiple projects with the same owner, why enter the name address over and over again? Simply enter the customer information one time into the customer table, then reference it with a customer ID number.
This may sound overly technical, but here’s why they call it a “relational” database. Each table you create should have a column designated as the “primary key.” This column holds a unique number that identifies each row (called a “record”) in the table. A RDMS uses primary keys to bring all the data together for you.
In a roofing business, a “project” table will have a primary key that consists of a unique project number. The project table is joined to another table by a “foreign key.” A foreign key is simply the primary key from one of the other tables, such as the customer table. Hence, the tables are “related” to each other. In our roofing business example, the project table would have a foreign key called “customer ID” that is actually the primary key from the customer table.
What does this mean in plain English? In the relationship between the customer table and the project table, each project should have only one customer (building owner), but each customer may have many projects. This is the most common type of relationship in a RDMS.
Roofing ApplicationsA database could benefit a roofing contractor in many ways. One of the most obvious benefits would be to track important customer data. Certainly it would include the contact name, and all the address information, but it could also track customers by the number of buildings owned, the roof system on each building, even which design consultant was used on each project. In addition:
The database could quickly sort which owners use built-up roofs, and put together a direct mail piece that advertises your qualifications and expertise on BUR projects.
Production rates, or squares per man per day, could be analyzed in a number of different ways. One could quickly determine average production rates for projects that are two stories high, use a 2-ply cold-applied modified bitumen, and have an average flashing height of 24 inches.
A contractor’s biggest profit killers, callbacks, could be analyzed for improvement. Callbacks could be tracked by owner, by city, by branch, by roof system type, the list goes on. Use it to determine which repairmen can produce which type of repair most efficiently.
Profit margins vary from project to project. A RDMS would allow many different types of profit analysis. Which system by manufacturer is most profitable? Which building type is most profitable: schools, plants, or commercial office buildings?
Estimating accuracy can be improved by using historical data to produce better production estimates on jobs you are bidding. For example, you could quickly review the average squares per day achieved on all single-ply projects that used polyisocyanurate insulation, on a metal deck, were under 100,000 square feet in size, and had an average flashing height of 8 inches.
SoftwareIf you are new to databases, I would suggest beginning with Microsoft Access. Many people already have it on their computer as part of the Microsoft Office software bundle. It works well for smaller applications. It is relatively easy to learn, and offers a rich variety of data management capabilities. It is easy to generate reports that display only the data you want. It’s not really a multi-user type database though, and does not have the ability to “rollback” record changes to a state just prior to a network wide system crash.
A number of other database products are available for contractors. In addition to Access, Microsoft offers SQL Server for larger applications. Oracle is the market leader (31.1 percent) followed by IBM (29.9 percent), Microsoft is third (13.1 percent), with Informix and Sybase coming in fourth and fifth.
Quickbase (Intuit) is an example of a database delivered by an Application Service Provider (ASP). This means that the software is entirely online, all you need is a browser and an Internet connection. There is also no expensive equipment to buy, and you can rest assured your data is protected and secure. Check it out at www.quickbase.com.
The FutureThere are a number of technologies under development or being implemented that will enhance a RDMS. In the future, it will be possible for a project foreman to scan material deliveries with a bar code scanner and wirelessly transmit that data into your database. You could then track material on hand at the job site, material that’s been installed, and compare it to your project requirements. Accurate delivery requirements could be forecasted and sent to your distributor, all done electronically, of course.
Daily production rates could be transmitted into the database, enabling accurate forecasts of your completion date and profit margin (or loss).
Medium and large commercial roofing companies may already have all the tools they need to begin implementing a database. Start small and consider using a database consultant to get the most from your database.