VISUAL QUICKPRO GUIDE FileMaker Pro 7 Advanced FOR WINDOWS AND MACINTOSH [Electronic resources] نسخه متنی

Implementing Relationships

When you created your flowchart of relationships, you outlined the most obvious places where the data in individual tables would intersect. When it comes to implementing these relationships, you need to consider not just what the relationships will be, but how you want to express them in layouts and where you want them to join.

Thinking ahead about sorting and searching

When planning your database, you should be thinking about which pieces of information will be used to find certain records and which may be used to sort data. Then you can make sure that those pieces of data are in separate fields.

For example, you will probably want to sort and search using such criteria as Last Name (for alphabetical listings) or Zip (for mailings). That's why it's critical that these sets of data be in separate fields, and not combined with the first name or the city and state.

Another useful field is one that lists the high school that a student graduated from, because you can generate reports that let you track them as alumni later. These fields should be included from the outset, so that you don't have to go back and laboriously enter the data later.

Tip

If you don't want your finds and sorts to take forever, look at your data relationships for likely search combinations before you break data into different tables. Indexing is the key to a speedy search.

Using key fields and relationships

Once the tables in a database are created, you need to establish the relationships among them. In this example, we noticed early on that the Class data contained very little unique information. Therefore, it will have two main relationships: to the Student table (using the Student ID as the key field) and to the Course ID. The bulk of the data found in the Class table will be a result of lookups from the other related tables. (The link to the Faculty table is indirect, since the Instructor ID field in Courses can be used to connect faculty information to the Class table.)

The Student ID will look up the rest of the student information and copy it into the class roster. At the end of the class, grades and credits earned will flow back to the Student table so that you can list which courses a student has taken, and summarize how many credits have accumulated. When you enter the Course ID in the Class file, the Course ID relationship will copy the course information (name, description, and credits) into the lookup fields (Figure 2.6).

Using lookups vs. relating data

By definition, building a relational database is based on the idea of using related data. Ordinarily, relating data is a much better strategy than relying on lookups, even though lookups are initially easier to create on the fly. One major problem with lookups is that they defeat one of the main purposes of a relational database, which is to avoid duplication of effort. Because you copy information from one file or table to another in a lookup, you freeze that data in the second one. Unless you update your lookup each time you change the original (and we can hardly think of a greater waste of time), it's not long before the information is incomplete, flawed, and ultimately useless.

Despite these negatives, there are a few situations where you should plan to relate data using lookups. When a database entry needs to display related information that shouldn't be constantly updated, lookups are perfect. In our college example, once a course roster has been completed and the time period during which a student can drop or add a course elapses, data from the roster should become part of the student's historical information. That's why the Classes table is important: It combines selected student, course, and instructor information. When you create a new record in this table, the student ID number and course number are entered. By using a lookup from the Courses table to the Classes table for information like the instructor name and course credits, you have an archive from the time that the student took the course.