Relational Databases Explained

Before you dive head first into relational databases, it will be helpful to review some vocabulary. First, a database is a collection of tables, layouts, and other things that forms an organized system. A table holds information about one kind of thing, like people, orders, products, or suppliers. A field holds one attribute of something: the person's first name, the order date, the color of a product, or the supplier's address. (An attribute simply means an individual characteristic. For example, a bicycle might have several attributes: Color, Height, Style, and Price. In a database, each of these attributes gets its own field.)

In the previous chapter, you've created a database whose tables and fields track various attributes of people. You could repeat the process and build any number of individual databases for organizing your time, creating invoices, and logging payments. But that approach has real problems, like the following:

  • When you log some billable work, you have to type in the customer information. Then, when you create an invoice, you have to type the customer information all over again in the invoice file. When you receive a payment, you have to type it a third time in the payment file. Since the databases aren't connected in any way, they can't share that information with one another.
  • Suppose you want to see how much you've billed a customer over the years. You could use the Invoices database and run a summary report by, say, the customer's name. But duplicate names, misspellings, and name changes would render the information useless. You could use the People database instead. After all, the total amount billed (or outstanding balance) is, in some sense, an attribute of a customer. But to have that information ready when you need it, you'd have to create a field for it in the People database and retype the invoice information there every time. You know you have all the information somewhere, but because the system isn't integrated, it can't help you put all the pieces together.

Even if you could live with these limitations, you're certain to run into trouble when you try to create the Invoices database. An invoice typically encompasses information about two different entities: the invoice itself, and each line item. There's simply no good way to track invoices without involving at least two tables. Your solution to all these problems? Hook multiple tables together into one big database.


Note: Database developers use the word entity to mean "one kind of thing." Person is an entity, and so is Invoice. Remember, though, that one specific thing isn't an entityBill Gates isn't an entity, and neither is Invoice #24601.


Not only can you put more than one table in a databaseand track more than one kind of thing in the processbut you can also tell FileMaker how the data fits together. You can say, for example, that invoice records have attached line item records; or that each payment record is associated with a particular customer record.

In database parlance, you define relationships among the data. A relationship is a connection from one table to another, along with the rules that define how records in the tables go together. For example, suppose you have two tablesInvoices and Line Itemsas shown in Figure 7-1.

In order to figure out which line items belong to each invoice, you need to understand the relationship between these tables. Notice that the Line Item table has a field called Invoice Number. This field holds (surprise!) the invoice number for each line item. You can also show this relationship with a picture, just like the one in Figure 7-2. By defining this relationship, you've created a mechanism to hook together an Invoice record and a Line Item record.

Figure 7-1. Each box in this picture represents a table. The name of the table is on top of the box. The fields are listed inside each box. When you design a multitable database, as you'll learn later in this chapter, you can use boxes just like this to map out your plan.

Figure 7-2. This picture shows the same tables, but it also shows how they're related to one another. The line between the tables represents the relationship. In this case, it shows that the Invoice Number in the Invoices table is connected to the Invoice Number in the Line Items table.


Part I: Introduction to FileMaker Pro

Your First Database

Organizing and Editing Records

Building a New Database

Part II: Layout Basics

Layout Basics

Creating Layouts

Advanced Layouts and Reports

Part III: Multiple Tables and Relationships

Multiple Tables and Relationships

Advanced Relationship Techniques

Part IV: Calculations

Introduction to Calculations

Calculations and Data Types

Advanced Calculations

Extending Calculations

Part V: Scripting

Scripting Basics

Script Steps

Advanced Scripting

Part VI: Security and Integration

Security

Exporting and Importing

Sharing Your Database

Developer Utilities

Part VII: Appendixes

Appendix A. Getting Help



FileMaker Pro 8. The Missing Manual
FileMaker Pro 8: The Missing Manual
ISBN: 0596005792
EAN: 2147483647
Year: 2004
Pages: 176

Flylib.com © 2008-2020.
If you may any questions please contact us: flylib@qtcs.net