If your data has grown beyond a spreadsheet, then you should be thinking about a relational design and normalized data.
The FileMaker Support Knowledge Base provides some articles on designing relational databases you may find useful:
- Relational Database Design 101 (part 1 of 3): Designing a Flat File Database
- Relational Database Design 101 (part 2 of 3)
- Relational Database Design 101 (part 3 of 3)
FileMaker has the capacity to be used in creation of relational databases, i.e. a database comprised of multiple connected tables. Think of five separate spreadsheets that all share common data and can be pulled together for the purpose of creating a report displaying data from all five sheets in a logical summarized printout. Most data requirements pretty quickly call for a relational structure rather than just a single table or “flat file” architecture. Whether you are thinking about using FileMaker to construct a relational database or using MySQL or Oracle, the rules, theory and logic that drive how you define the relationships between your data entities and the tables will be the same.
Planning is key with relational databases and for this database developers use a set of visual shortcuts to sketch out their database structures in what are called ERD diagrams (Entity Relationship Diagrams). In separating data entities into separate tables and then drawing lines that show how the data from each entity is related to data in the other tables, a developer can begin to envision how to make an architecture that will support a user’s data-entry, reporting and workflow needs, eliminating data entry redundancies and reducing the opportunity for data entry errors as well.
If you are not a full-time developer, approaching a data modeling project on your own may be fun but not the best use of your limited time. If you are set on doing this, however, the literature on how to do this abounds. FileMaker Inc. has good documented resources about how to plan for and then create relational databases in FileMaker.
Even if you are working with a database professional, you may be asked to think about your data in terms of entities, tables and relationships so being a bit familiar with these terms will be helpful. Here are some key concepts about planning a relational database that are useful to know.
- Always break your data down into the smallest meaningful nuggets possible.
- Group similar nuggets together in tables, e.g., one table contains the names and addresses of students another contains a list of courses that students have taken.
- If you find data is being repeatedly entered into many records in a table (you are entering a course name many times, once for each student who took the course, for example, you will need to normalize the data by creating a courses table which will be related then to a courses taken table, requiring you to enter that course name only once.
- 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.
- At MIT using numbers such as MIT ID can allow you to access and relate to data tables that may be stored centrally in the Data Warehouse using FileMaker and the same relational logic used to set up your local FileMaker tables.