All businesses, from mom-and-pop storefronts to global corporations, need to manage data. For decades, database management systems (DBMS) have served as important methods of data management. DBMS software automatically stores and organizes data and protects it with sophisticated security. Today, several different types of databases are available on computing devices ranging from smartphones to large-scale mainframes.
Why a Database?
When you need to record and organize data for your business, you might use a spreadsheet app such as Microsoft Excel, Apple Numbers or Google Sheets. In some instances a spreadsheet is fine; however, many businesses find that they can be limiting and unwieldy. For example, an online store with millions of customers will keep its data primarily in a database, not in spreadsheets. Databases can handle large amounts of information with great speed and flexibility, organize the data more efficiently and offer security and integrity that spreadsheets can’t touch.
The Client-Server Model
Most database systems are organized according to the client-server model. A program called the server controls all aspects of the database, including where the data is stored, how it’s organized and how much security it has. The server grants no access to the data without the proper commands and passwords, and it provides only the data that’s requested. The client is a separate program that makes requests to the server. Although you can have a system consisting of only one client and one server, it's more common to have multiple clients and one server, or multiple clients and servers.
Security and Integrity
Database server software protects data with sophisticated security. You can assign different passwords to users or user groups, and allow different levels of security for different types of data. In addition, most databases let you create rules that automatically check your data as you add and update it, helping to minimize errors. For example, you can create a rule for the state in an address, that it must be a correct U.S. state abbreviation. This prevents erroneous states such as NA or B2 from entering the database.
Different Types of Databases
A common type of database is the relational kind, which can temporarily join data stored in separate tables into complex and useful combinations. For example, you can connect salesperson, customer and order data to create a list of salespeople and their biggest customers. Although some databases store data as numbers, such as dollar amounts or dates, and text, such as name and address, others can store video, documents and other complex information. Earlier network and hierarchical databases were similar to the relational kind but less flexible in terms of combining data.
Database Design and Organization
Database design is a task typically undertaken by a software developer, app designer or database specialist. Whatever her background, she compiles a list of requirements from the users and creates the database organized into functional parts, such as separate data tables for customer, inventory and order data. To that she adds any needed security, data-checking rules and programming code used to build the apps that work with the database.
SQL: Structured Query Language
Many relational databases use the Structured Query Language (SQL), an English-like programming language used to create, maintain and retrieve data from the database. In the client-server model, a client app creates a message in the form of a SQL command and sends it to the server. The server receives the message, checks its security passwords, executes it and passes the results back to the client. A simple SQL command to list customer information from the database might look like this:
SELECT name, address, city, state, zip-code FROM customer-table ORDER BY name;
Examples of Data Management Tools
Most databases include software tools to help you manage your data. One such app allows ad-hoc queries. For example, you might need to know how many of your Kansas customers bought green widgets last December; the query tool lets you enter SQL commands to get the answer in seconds. Other management tools let you alter the database structure, tune it for best performance, maintain security and measure how much data you have.
Chicago native John Papiewski has many years' experience in IT consulting, and has worked with businesses including finance, real estate, distribution and publishing. His articles have appeared in various outlets including azcentral.com and seattlepi.com. Please, no workplace calls/emails!