Relational Data and SQL

Most, if not all, business applications function by processing business data. In fact, early business solutions were described as data processing applications. The efficient storage, processing, and communication of data is such an important part of running a business that companies make huge investments in developing computerized systems to handle those tasks in the most effective manner possible.

One of the ways data processing can be made more effective is by ensuring that an efficient mechanism for storing and retrieving data is used. By far, the most common approach to data storage is to use a relational database, such as those managed by Microsoft SQL Server 2000, and structured query language (SQL) to retrieve and manipulate the data contained in the database.

You’re probably familiar with relational databases, and because there are many excellent reference books about SQL Server already available, I won’t delve too deeply into a discussion of its features here. In any case, the database itself is simply a tool for storing data; the important thing to concern ourselves with is the actual data itself. Data is central to the success of a business or an organization, so we need to understand what it actually represents.

The data that is stored and processed by a business represents the real-world entities that the business is concerned with. When designing a database for a business, your first task is usually to identify the entities that need to be represented. For example, a retail organization might use data to represent retail industry entities, such as products or suppliers, while a bank uses data to represent banking entities, such as accounts or customers. The entities can be thought of as classes, or types, that define the important things the business needs to deal with.

The database designer then identifies the individual properties or characteristics of an entity. These characteristics reflect the properties of real instances of each entity that are important to the business. For example, each customer in a bank might have a name, an address, and a telephone number, while each product in a retail database might have a description and a price. To make the data as granular as possible, most database designs break the characteristics down into nondecomposable units. For example, a name property could be broken down into firstname, middle initial, lastname, title, and so on. Usually, one or more properties are used to define a unique characteristic for each entity. For example, each product could be assigned a unique Product ID. This is the primary key by which an individual instance of the entity is identified.

Finally the database designer identifies the relationships between instances of the entities. For example, in a retail database, a product has a supplier. At this stage, the model can be refined to include some conceptual entities that enable the relationships. For example, the designer can create an order entity to facilitate the relationship between a customer and a product (because a customer places an order for a product).

This logical model of the business is implemented physically in the database when the designer creates tables to represent the entities. Each characteristic of an entity is represented as a column, and each instance of an entity is represented by a row of data in the table. Relationships between the entities are represented by using common key columns in the tables.

The logical and physical design of a database is an extremely complex task, and I’ve only mentioned the main points briefly. You’ll find some excellent documentation on the subject in the "Creating and Maintaining Databases" section of SQL Server Books Online.

The relational approach to storing data has proved to be one of the most important developments in building business software. Most large organizations in the world use relational databases for most of their business data, and the relational database is the foundation for most of the business applications written today.