Download
this construction software article
|
 |
| |
 |
See why contractors use FOUNDATION as their construction accounting software!
(click here) |
|
 |
|
| |
 |
| |
|
Taking the Mystery out of Your Database
It stores your all-important data, but
what else should you know about this
system that lurks in the shadows?
BY FRED ODE
As the chief information officer
for Lighthouse Electric,
Inc., in Canonsburg, PA,
Ron Felix had a problem. His accounting
and job costing software worked
just fine for day-to-day data entry and
even produced a fair amount of standard
reports. But as the business
grew, the tool just couldn’t keep up.
While the reports showed a clear
overview of the company’s current
status, they didn’t allow him to drill
down to the level of detail needed to
analyze the future of their projects. “So
many times I get unique requirements
for a report, and I had to get the information
quickly. We needed to produce
different reports every day,” Felix said.
In today’s competitive economy,
having detailed information at your
fingertips is essential to your company’s
success. You must be able to
make decisions and projections based
on timely, accurate and detailed information.
The good news, as Felix
learned, is that the current relational
database technology provides a secure
environment for storing large amounts
of data and offers powerful tools to
retrieve just the data you need when
you need it. Felix led his company’s
switch from software based on a flat-file
storage to one based on Microsoft SQL
Server. Finding the exact data the company
needs at any given time is no
longer a problem.
A relational database allows Felix
quick access to information because of
the structure in which the data is
stored. Broadly speaking, there are two
types of databases—flat-file and relational. To simplify the definitions of each, a flat-file database
typically uses text-type files that simply list long
strings of text and numbers, often separated by commas
or tabs. Think of this as a document typed up in a
word processor. In a relational database, different
types of data are stored in different tables, and relationships
between tables are easily defined. A table is
more like a spreadsheet. Each column represents a
different category of information, and the values in a
row are related.
Consider a system to record the jobs you’ve done
for a customer. In a flat-file database, each job, its customer
and related information might be stored as one
line in a text file. In a relational database, however, there
might be one table listing all jobs and their information.
There may be “columns” for job number, job name, job
address and customer number. Another table would list
the customer information, including the customer number.
The customer number, which appears in both
tables, provides a link, or “join,” between rows in the
two tables. While this difference may seem minor on the
surface, relational databases offer many advantages.
Unique data combinations
Storing data in separate tables linked by key information
allows you to build reports quickly and with more variety.
As data gets more complex, the relationships between
the tables can become more complex, something that
cannot be easily accomplished in a flat-file system.
Recently, Felix received a request for a list of employees
that lived near a particular job site. In his old,
flat-file system that would not be possible. There was
just no way to get at the data to define the relationship
between the employee’s address and the job address.
With a relational database, however, Felix could easily
create a list of employees based on their location.
Third-party tools
Relational databases are designed to allow querying of
the data for precise information. As a result, a multitude
of tools for accessing data have been created.
Microsoft SQL Server works particularly well with other
Microsoft products such as Excel. Using a wizard,
Excel users can easily select data from a SQL Server
database and populate a spreadsheet.
Other tools such as Seagate Crystal Reports are
more robust, offering even more flexibility but with a
higher learning curve. Says Joe Bruno, vice-president
of the Ohio Crystal User’s Group, “A savvy user can
pick up Crystal and understand it in eight hours or so.
Once you know how to get at the data in the database
and understand SQL queries, building and formatting
reports is a snap.”
Security
Because flat-file databases often use simple text files, they
may be accessible through word processors. If someone
can find the file on your network, they have access to your
data. High-end relational databases, on the other hand,
usually have built-in security that hides the data from
those without an ID and password. Furthermore, the
security is customizable, so you may allow access only to
specific data or give permission to view but not modify it.
Data integrity
In addition to security from intruders, relational databases
have built-in tools to prevent corruption of data.
Because relational databases allow complex relationships
between tables, data usually isn’t duplicated
across multiple tables. However, since those relationships
are often not possible in flat-file systems, programmers
must duplicate data from one flat file to
another—and that inevitably leads to inefficiency and
inaccuracies. Felix recalls the maintenance and regular
rebuilding of files required on his old flat-file system. “SQL Server, on the other hand,” says Felix, “just runs.
It’s virtually maintenance-free.”
Powerful reporting, security and data integrity are
fundamental advantages of relational databases, but
the real benefit lies in what the information offers your
company.
Common database terms
If you’re looking at relational database
technology, these terms are must-knows
Field: A specific category of data In the jobs table, the job number,
the job name and the job status might each be a field.
Index: A feature used by database tools to make searching data
faster. Basically, it tracks information entered in tables so that individual
records can be found more easily.
Join: A connection between tables based on information that is the
same in both tables. The ability to define many unique joins between
data is an important advantage of relational databases.
Key: (Also known as “primary key”) A field in a table that is used to
uniquely identify each record in the table. The job number might be
the key for the jobs table.
ODBC: Open DataBase Connectivity, a set of standards that allow
databases to be accessed by other tools. For example, Microsoft SQL
Server is ODBC-compliant and can therefore be accessed by other
tools that follow ODBC standards.
Query: A statement, posed in a specific format, that accesses
tables in a database and finds specific data. Users build queries to
create needed reports.
Record: A collection of data for one specific object in a table. If
you think of a table as a spreadsheet, it represents one row in that
table. In the jobs table, each job would be a record.
Rollback: A feature of many relational databases that allows
users to discard any changes that were made to the data up to a
certain point. It provides another level of data integrity because
changes that were made incorrectly can be “undone.”
SQL: Structured Query Language, a computer programming language
that allows users to work with relational databases. Queries
built in SQL will retrieve specific data from a database for reporting,
etc. Microsoft uses the SQL language (along with proprietary
changes) to create and maintain Microsoft SQL Server databases.
Table: A collection of one type of related data. You may think of a
table as a spreadsheet that contains categories of information (fields)
for many rows of related objects (records). One or more tables make
up a database.
Fred Ode is the founder and chairman/CEO of Foundation
Software, Inc. Ode developed a construction-specific
accounting software, Foundation for Windows,
that suits a range of trades. For more information, visit
www.foundationsoft.com or call 800-246-0800.
|
