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:
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. |