FileMaker - Relational Databases

ITAG Standard #10: Applications needing to use relational databases must use MIT's standard database server technology and data models.

Data Modeling with FileMaker

All good database design depends on proper data modeling. Developing a FileMaker solution, even a small departmental system, should be no exception to traditional standards and best practices. If your data has grown beyond a spreadsheet, then you should be thinking about a relational design and normalized data.

Relational Database Theory

Developed in 1970 by E. F. Codd at IBM, the concept of relational databases broke data management out of the confines of hierarchical structure. By separating data into tables and imposing some rules around key fields, the model itself could facilitate the retrieval, updating, and reporting of that data through SQL commands. The process of database normalization, while increasing the number of tables, progressively reduces data value redundancy.

What does that mean in English? If you keep all your data in a single table (in pre-FileMaker 7 terms, that means a single file), your data entry and reporting is probably more cumbersome than it needs to be. If you need to do more than sort your data --for example, if you want to retrieve particular subsets of your records, or cluster them in varying groups-- then you should apply a relational model.

What are the benefits of employing a relational model? By eliminating redundancy, your data becomes easier to maintain and your reports are more streamlined. By enforcing data integrity with key fields and unique identifiers, your search results are always what they should be.

Applying the Relational Model to FileMaker Solutions

The goal is to break you data down into the smallest meaningful nuggets possible. Then group the nuggets of like "nature" together in tables. For example, you may have a software inventory database. In that database, you will have one table that contains the names of different software applications and another table that contains the names of software manufacturers. Your model will "join" the software to the appropriate manufacturer through the use of "key fields" containing unique values that never change. A visual representation of a database's data model is called an Entity Relationship Diagram (ERD). An ERD is a very useful way to map out how to accomplish the intended functionality of your database. If you can't see from your ERD how your system will generate a particular report accurately or allow a user to update records correctly, then you need to rework your ERD before the design and development stages of your database.

Relational Theory gets far more complex than your solution may actually require. So, here are a couple basic guidelines to follow so that you are creating a proper data model:

• Each table must have a primary key field to use for joining to other tables in relationships. The key field must require a value and must be unique for each record, called a "unique identifier." This value must never be changed. Auto-generated serial numbers are good for this purpose. So, if you have a list of software manufacturers, your unique identifier for Macromedia, would not be Macromedia, it would be 287. Another field in that record would contain the name Macromedia. Never use a person's name (names can change or apply to more than one person). Never acquire and use sensitive data like a social security number. MIT ID numbers are good for this purpose.
  
  
• If you find that data is being repeatedly entered into many records in a table, normalize it by separating it out into another table and relate to it instead. So, if you find yourself entering Adobe Acrobat, Adobe Photoshop, Adobe ImageReady..., you may need to have a "manufacturers" table separate from your "software" table. You would enter Adobe once in the manufacturers table and then store Adobe's unique identifer in the software table. That way you don't run the risk that in the course of typing "Adobe" twelve times, misspellings may occur that might compromise your inventory reports.