This chapter is from the book
This chapter is from the book
This chapter is from the book
Working with Tables
As you saw in the preceding chapter, your database consists of tables, each of which is made up of rows or records with columns or fields that contain the data. A database can consist of a single table or a number of tables.
By default, when you create a new database, a single table is created that has the same name as the database. That actually might not be what is best. You might want to rename that default table so that it fits into the naming convention of all the tables in your database.
Table Naming Conventions
The Manage Database dialog lets you create and name (and rename) fields and tables. It is a good idea from the start to enforce some naming conventions on both fields and tables.
FileMaker Pro's flexibility with regard to things such as legal characters in names and the length of names for tables and fields can be too much of a good thing. You can use up to 100 characters in a name, but chances are you will need far fewer for your actual names.
Here are some suggestions based on conventions used by various FileMaker developers. There is more information in the Support area of the FileMaker website; in addition, FileMaker's TechNet membership gives you access to still more information and guidelines. Pick what are the most useful conventions, but stick with them.
Stick with them, that is, within a single database or even a project. One problem with implementing design conventions is that the world is a large place, and it is likely that your naming conventions will need to interact with naming conventions of other systems and databases. Being internally consistent keeps your own house in order. That is the most that you can hope for, unless you volunteer to serve on a committee that drafts conventions for your organization, industry, or other group.
Naming tables is simultaneously simple and almost irrelevant. The reason is that as soon as you have a database with more than one table in it, you will most likely be using the Relationships graph (described in Chapter 7, "Working with Relationships"). The Relationships graph initially shows each table with the name that you assign to it. However, you will create additional instances of your tables in the Relationships graph, and you will name each of them. In practice, you will usually be working not with the base table, but with the additional instances.
For example, you might have a table called Personnel. In the Relationships graph, you might have instances of this table called PersonnelByID, PersonnelByName, PersonnelByDepartment, and so forth. Practically, you could name the base table Table 1, and, as long as the other names appear in the Relationships graph (and in your code), everything would be clear (but this is presented only as a hypothetical example, not a good practice).
When you create a database, by default you will wind up with a database, a single table, and an instance in the Relationships graph all with the same name. Many people begin by renaming that first table right away. Here are some of the suggested standards:
- Use only the characters 0–9 and a–z (both uppercase and lowercase).
- If table names contain several words, separate them with underscores or with intermediate capitalization (as in personnelSalaryInfo or personnel_salary_info).
- Be consistent in capitalization and number (that is, use table names such as Contacts or Contact, contacts or contact).
- Do not use special characters or reserved words in table names. Reserved words include FileMaker reserved words as well as words that might be reserved in SQL or other languages you can use to access the tables. Select is not a good table name because, although it might be useful for storing selection values for records in your database, it is an SQL reserved word.
In addition, consider whether you want to place any descriptive information in the table name. If you do so, the usual convention is to place it at the end following an underscore. This approach is particularly useful if you separate words within the table name using intermediate capitalization. For example, inventorySuppliers_pub and inventory_Quantities_pri are reasonable names for inventory tables that, respectively, contain the publicly available names and addresses of suppliers and the private quantities of inventory items on hand. You can enforce access to these tables with your security accounts and privileges, but it can be useful to indicate not only what is in the tables but also the sensitivity of the data.
Creating New Tables
To create a table, go to the Manage Database dialog (File, Manage Database). Click the Tables tab to show the view shown in Figures 3.2 and 3.3. Note that this is one of the places in which FileMaker Pro and FileMaker Pro Advanced differ.
)
Figure 3.2 Use the Tables tab in Manage Database to create, change, and delete tables in FileMaker Pro.
)
Figure 3.3 Use the Tables tab in Manage Database to create, change, and delete tables in FileMaker Pro Advanced.
To create a new table, enter a name for the table at the bottom. Click Create, and your table will be created in the list of tables. An instance of the table will also be created automatically in the Relationships graph. To rename a table, highlight its name in the list of tables in the Tables tab, type in the new name at the bottom, and click Change.
To delete a table, highlight its name and click Delete. If you want to print out the fields in one or more tables, highlight it (or them) and click Print.