This chapter is from the book
This chapter is from the book
This chapter is from the book
Working with Fields
The heart of the database is the data within it, data that is stored in fields. This section provides some basics about working with fields.
Field Naming Conventions
The naming conventions for tables with regard to spaces, characters, capitalization, and so forth apply also to fields. There are some additional considerations when it comes to naming fields. Specifically, they have to do with the identification of field types and the naming of internally used fields.
Many developers use abbreviations for data types in field names. Often it's handy to know the data type of a given field when working with it without having to refer to the Manage Database dialog. Here we've used "t" for text, "n" for number, and "c" for calculation:
- ProductName_t
- Price_n
- TaxRate_n
- Tax_c
Some developers also note whether a field is indexed ("x" for indexed, "n" for unindexed):
- Location_Name_tx
- Location_Desc_tn
- Location_Size_nn
Some naming conventions also break out a division between data fields and what are commonly referred to as developer fields—those fields that you need only to make your FileMaker Pro solution work. If you ever went to import your database wholesale into another system, you would probably leave behind the developer fields. Here, we have two abbreviations: "k" for key (or match field) and "zz" (so that it sorts to the bottom of the list) for developer utility fields. We also use underscores to ensure that keys sort to the top of our field list, with the primary key coming first.
|
|
To understand how keys are used to identify records in tables and form relationships, see Chapter 5, "Relational Database Design," p. 143. |
- __kp_primary_AlbumID
- _kf_foreign_ArtistID
- AlbumName
- Date
- zz_SelectedPortalRow
- zz_UserColor_Preference
- zz_UserGenre_Preference
Many developers use a minimal set of field-naming standards. It relies on leading lowercase characters to indicate the field type. If you choose to use that minimal set, here are the conventions used:
- g—Global
- c—Calculation
- s—Summary
- zz—Internal use (This causes the field name, when shown in an alphabetical list, to be at the bottom. If you use a single "z," your internal fields will be interspersed with fields such as ZIP code.)
Descriptions of field types might or might not use this set of standards, which you can add to the end of the field name following an underscore:
- t—Text
- n—Number
- d—Date
- ts—Timestamp
- tm—Time
- c—Container
Putting these together, you could have field names such as these:
- creationDate_d
- gProcessingOffice_t
- gcNextInvoiceNumber_n
You can even go further by not bothering with field types where the field name already includes it. creationDate_d really adds no information to creationDate.
Whatever you do, be consistent. The point is not to create a set of naming conventions that overshadows the database but, rather, to create naming conventions that help you and future developers build and maintain the solution.
If you're planning on using FileMaker Pro as a web back end, refer to "Problematic Field Names" in the "Troubleshooting" section at the end of this chapter.
|
|
For more information on using databases on the Web, see "Designing for IWP Deployment," p. 532, as well as Chapter 25, "Custom Web Publishing with XML/XSLT," p. 545. |
Adding Field Comments
Notice also that you can add comments to your field definitions. (A field comment was shown previously in Figure 3.1.) Commenting is a vital discipline to develop. Spending a few moments to add information to the Comment text box, below the field name, as you create a field will save time later in trying to figure out what you were thinking at the time. Don't bother repeating information that is in the field name. If the field represents pixels or pennies, adding that information to the field name might be worthwhile (as in Width_In_Pixels). Use comments for in-progress remarks (such as "Added 4/1/2007 JF for task restructure" or "for reporting only").
){kind=link}
Creating New Fields
To create fields in FileMaker Pro, you need to enter some text in the Field Name area of the Manage Database dialog and click Create. One important aspect of databases to keep in mind is that it's important to establish a discrete field for each bit of information you want to store. If you create a field called Contact Information and cram an entire address and a set of phone numbers into it, technically it will work fine. But if you ever need to export that information, sort by area code, or run a report by city, you won't be able to cull the information you want from the field without suffering a good headache.
|
|
To database wonks, the Contact Information example would be a violation of first normal form, or more colloquially, "one fact, one field." For information on relational data modeling and defining fields, see "Relationship Types," p. 149. |
As shown previously in Figure 3.1, the Manage Database dialog lets you create, change, duplicate, and delete fields. As with the Tables tab, FileMaker Pro Advanced has additional buttons: Copy and Paste. If you select a field or fields, you can click Copy and paste the fields into the same or another table. Pasting them into the same table is the same as duplicating them.