FileMaker Pro Database Table Design

FileMaker Pro is famous for its ease of use. However, if you plan to use this program to create a useful database solution, it is important that you resist the temptation to dive straight in and spend a bit of time planning your database. This is the only way that you can be sure that the database management system (DBMS) you end up with will be suitable for your purposes.

One of the first stages in the process of database design is the creation of tables and the categories or columns they contain. Basically, your database needs to store information and allow retrieval of this information in a useful fashion. At an early stage in the design process, you therefore need to identify every single piece of information that is important for you. This information will fall into different groups or categories. When you come to build the database, each of these categories will become a table.

Database tables consist of rows and columns, just like an Excel worksheet. The columns are often referred to as fields, while rows are often referred to as records. In a really simple database, only one table may be required; but this is pretty rare; most databases require several tables, each containing information relating to one aspect of the overall system. It is very important to make sure that you do not bundle information relating to different aspects of the project into the same table when designing your DBMS since this may lead to duplication and loss of data integrity.

Version 1 and 2 of FileMaker saved all information in one table. It was only with the release of version 3 in 1995 that FileMaker offered the use of multiple tables. In its current release, version 10, FileMaker automatically creates a single table for you whenever you create a new database. The table has the same name as the database itself and contains no fields. Your first step will probably be to rename this table and define the fields you wish it to contain. To rename a table, select the Tables tab in the Define Database dialogue, click on the current name, enter a new one and then click the Change button.

To create fields, select the Fields tab in the Define database dialogue, enter the name of each of the fields that you want the table to contain, specify the data type of the field (Text, Number, Date, etc.) then click the Create button. Be sure to include a primary key field in each table you create. This will be used to relate the table to other tables to design your final solution. To create a primary field, enter a name, set the field type to number then click Create. Next click the Options button and in the Auto-Enter tab, activate the option \”Serial Number\”. Finally, in the Validate tab, activate the options \”Not Empty\” and \”Unique Value\”.

The writer of this article is a developer and trainer with Macresource Computer Solutions, a UK IT training company offering FileMaker Pro Classes at their central London training centre.