Structuring Your Data

A data source that is capable of referencing only a single table is called a flat-file database and is of limited use. Most modern data sources can address multiple tables and are referred to as relational data sources.

Remember the EmployeeID field, singled out as the only numeric data type in our example table? Actually, the EmployeeID field is more than just a number it's the index or primary key, which is a unique field that identifies each record. The field designated as the primary key field is often established as a number field that automatically increments every time another record is added. Although the primary key could just as easily be a text field as long as the text is unique for every record best practice dictates that a primary key field has no other function.

Primary key fields are essential to using relational data sources; they are, in effect, the glue that relates one table to another. As an example, let's say that we wanted to track a customer's orders at our online store. Each customer would have a record in the customers table with, of course, a unique identifying number. You'd also need a table of orders that shows the items ordered and the amount paid, among other things. Hopefully, the customer will come back time and again, so the orders table would have multiple orders from the same customer. How do you identify the customer in the orders table without reentering the name and all the record information each time? You do this by using the customer's primary key value. Some database applications, like Access, provide a visual method for seeing how one table connects to another [c1-2].

Figure c1-2.

In our example, we show how we can track a single customer making multiple transactions; this is referred to as one-to-many relationship. One-to-many relationships are by far the most commonly used. Data sources can also include many-to-many relationships. With a many-to-many relationship, two tables are generally connected via a third, linking table. The linking table would contain primary keys from the other two tables in addition to any data that pertains to the intersection of the tables. A good example of a many-to-many relationship is the orders and catalog items correlation. A single order might include many catalog items and, hopefully, several orders. To relate the Orders table to the Items table, we need a third table, Sales. The Sales table contains a link to both Orders and Items [c1-3].

Figure c1-3.

Designing the database structure or schema properly is important to the efficient processing of your database queries. Here are some pointers to help you approach the design of your data source:

• Before creating the data source, it's best to decide the nature of the data needed and how items relate to one another. Work out the details on paper well before beginning to create a data source structure. It's often best to examine this issue from a perspective of how the data source will be used.

• Try to break each field down to its component parts; compound fields that combine city, state, and zip into one field, for example, are a major mistake because combined fields make it difficult to sort or search the field based solely on a single element, such as zip code.

• Play close attention to the data types involved; don't, for example, make the mistake of setting a phone number field as a number data type because it is composed mainly of numbers. Set the data type according to how the field will be used, not what's inside it.

• Be careful when naming a data source field. Avoid duplicate names, such as using ID for every primary key. It's also good to steer clear of commonplace words, such as date, which are often keywords in an application server or language and can cause errors.

• Whenever possible, use a separate table for information that might be required more than once. Be sure to include primary keys for each table and related keys between tables that are connected.

• Data stored in one table should not be repeated in another. The primary and related keys provide links between tables that allow you to display recurring information many times while only storing it once.

Proper data source design is often a shortcoming in Web applications; developers just don't pay as much attention to it as they should. A properly designed data source is more efficient, secure, and serviceable than one that is just cobbled together.