1.3 Basics of database development
Developing a database in FileMaker is a key step in managing and structuring information used in a company or organization. A well-designed database facilitates access to data, optimizes workflows and improves the efficiency of data processing. This section introduces you to the essential concepts of database development in FileMaker, from data modeling and defining relationships to designing layouts and implementing security measures.
Table of contents
- 1.3 Basics of database development
- What is a relational database?
- Normalization of the database structure
- Link types in FileMaker and SQL
- gFM-Business, relationships and SQL queries
- Layout design
- gFM-Business, relationships and SQL queries
- Layout design
- Scripting and automation
- Security measures
- Best practices for database development
- gFM-Business Open Source FileMaker Basis ERP The software for a crash course
- Optimization of the database
- Frequently asked questions about FileMaker database development
What is a relational database?
A relational database is a collection of data organized in tables. Each table contains rows (data records) and columns (fields). Relational databases use relationships between these tables to efficiently link and manage data. FileMaker is a relational database platform that makes it possible to create complex data models that meet the specific requirements of an organization.
Tables and fields
In a relational database, a table is a collection of data records consisting of several fields. A field is the smallest unit of data and contains specific information such as names, telephone numbers or addresses. For example, a "Customers" table could contain fields such as "Customer ID", "Name", "Address" and "Phone number". Each row in the table represents a single customer.
- Create tablesIn FileMaker, you can create new tables by opening the database administration tool and defining the appropriate fields. The structure of a table should be carefully planned to ensure that all necessary data is captured correctly.
- Field typesFileMaker supports different field types such as text, number, date, timestamp and container. Each field type is optimized for specific types of data. It is important to choose the correct field type to ensure the integrity and accuracy of the data.
Relationships between tables
The real strength of a relational database lies in its ability to link data across multiple tables. This is done by defining relationships between tables.
- Primary keyA primary key is a unique field in a table that uniquely identifies each data record. In the "Customers" table, for example, the "Customer ID" field could be used as the primary key.
- Foreign keyA foreign key is a field in a table that refers to the primary key of another table. In an "Orders" table, the "Customer ID" field could be used as a foreign key to link the order to a specific customer.
- Creating relationshipsIn FileMaker, you can create relationships between tables by using the relationships graph. Simply draw a line between the primary key of one table and the corresponding foreign key of another table. These relationships make it possible to combine data from different tables and create reports that contain information from multiple sources.
Normalization of the database structure
Normalization is a process that aims to optimize the data structure in order to avoid redundancies and ensure data integrity. This process involves several steps known as normalization.
- 1. normal form (1NF)In the first normal form, all fields should be atomic, i.e. each field should contain indivisible information. For example, first name and surname should not be stored in a single field, but in separate fields.
- 2. normal form (2NF)A table is in the second normal form if it is in the first normal form and each non-key field is completely dependent on the primary key.
- 3. normal form (3NF)The third normal form requires that no non-key fields depend on other non-key fields. This minimizes redundancies and reduces the risk of anomalies during data manipulation.
Link types in FileMaker and SQL
FileMaker provides a visual method for linking tables in the Relationship graphs. There, relationships are represented by link lines between the fields of tables. In SQL, on the other hand, relationships between tables are represented by JOINs where logical expressions are used to link records. Relationships in the FileMaker relationship graph are defined with the following dialog:
gFM-Business, relationships and SQL queries
The gFM Business ERP software has numerous relationships in the FileMaker relationship graph. The relationships are always named from the source to the target, for example Customers_Addresses for addresses that are linked via the customer ID.
Variables in gFM-Business are often defined directly via SQL query. gFM-Business allows access to all tables via SQL on the native table name, e.g. "Customers", "Addresses" etc.
1. FileMaker links in the relationship graph
In FileMaker, relationships can be defined in different ways. The most common types of relationships are equality relationships (e.g. =) and inequality relationships (e.g. >= or <).
Example 1: Equality relationship
- Relationship Customer addresses: This relationship is based on the equality of key fields in the two tables. This looks as follows in the relationship graph:
FileMaker relationship:- Customer::_pk_Customer_ID = Addresses::_fk_Customer_ID
This is a simple 1:n-Relationship (a customer can have multiple addresses). The relationship is represented in the FileMaker relationship graph by a line with a
=-symbol is displayed. In SQL, this corresponds to a SQL example:SELECT *
FROM addresses
WHERE
_fk_Customer_ID = Customer::_pk_Customer_ID
This displays all customers and their assigned addresses based on the matching customer ID. - Customer::_pk_Customer_ID = Addresses::_fk_Customer_ID
Example 2: Inequality relationship
- Relationship Customer actions via resubmission: This relationship not only uses equality, but also a comparison condition
>=. This looks as follows in the relationship graph:FileMaker relationship:
- Customer::_pk_Customer_ID = Actions::_fk_Customer_ID
- Customers::_Date_Today >= Actions::Date_Resubmission
In this case, an additional condition is added to ensure that only actions for which the resubmission date is before or equal to the current date are displayed. This type of relationship is indicated by a line in the relationship graph with a
>=-symbol. In SQL, this would be displayed with a WHERE-clause is expressed with two parameters:SQL example:
SELECT *
FROM Actions
WHERE
_fk_Kunden_ID = Kunden::_pk_Kunden_ID AND
Date_Resubmission >= _Date_TodayThis shows all actions that are linked to a customer and for which the resubmission date is today or in the past.
2. Links in the SQL context
In SQL, relationships have more flexibility in the use of relational operators. In contrast to FileMaker, where the relationships are visually displayed and predefined in the relationship graph, SQL allows you to use different operators within the JOIN- or WHERE-clauses.
SQL queries are created in FileMaker with the command
SQLAusführen ( sqlAbfrage ; field separator ; row separator {; Argumente... } )
is executed. The SQL query itself is located in the first parameter of the command within quotation marks. For this reason, all quotation marks must be escaped with a backslash (and the backslash itself), which should be inside your SQL query. Text keys must be enclosed with a single apostrophe, number keys must not.
Example:
SQLAusführen ("SELECT \"Name\" FROM \"addresses\" WHERE
\"_fk_Customer_ID\"='" & Customer::_pk_Customer_ID & "'"; ""; "")
Example: Comparative relationships
In SQL, you can not only use equality, but also inequalities in the JOIN conditions. These offer extended functionalities to filter data on the basis of time, quantity or status comparisons.
SQL Example of an inequality relationship:
SELECT *
FROM customers
INNER JOIN actions
ON Customers._pk_Customer_ID = Actions._fk_Customer_ID
AND Customer._Date_Today >= Actions.Date_Resubmission;
Only the data records for which the current date of the customer is greater than or equal to the resubmission date of the action are linked here.
3. Summary of the link types
- Equality relationships (
=)These are the standard method in FileMaker for linking data records. They are indicated in the relationship graph by a line with the=-symbol and correspond in SQL to a INNER JOIN. - Inequality relationships (
>=,<=)These extend the functionality and allow you to create links based on comparisons. In the relationship graph, they are indicated by a line with the corresponding comparison symbol. In SQL they are displayed via JOIN-conditions or WHERE-clauses were realized.
Layout design
Layouts in FileMaker determine how data is displayed and entered. A well-designed layout improves usability and ensures that data is captured efficiently and accurately.
Layout mode
Layout mode in FileMaker is the area in which you design the user interface of your database. Here you can place fields, add labels, create buttons and define the general appearance of the database.
- Add fields and objectsYou can drag and drop fields from your table into the layout. You can also add objects such as buttons, text fields and graphics to improve the user experience.
- Themes and stylesFileMaker offers predefined themes and styles that standardize the appearance of your layouts and make them more professional. You can customize these or create your own styles to personalize the design of your database.
- Navigation and user-friendlinessTo increase usability, you should include navigation buttons that allow users to quickly switch between different layouts. Clear and intuitive navigation is crucial to maximize user efficiency.
gFM-Business, relationships and SQL queries
The gFM Business ERP software has numerous relationships in the FileMaker relationship graph. The relationships are always named from the source to the target, for example Customers_Addresses for addresses that are linked via the customer ID.
Variables in gFM-Business are often defined directly via SQL query. gFM-Business allows access to all tables via SQL on the native table name, e.g. "Customers", "Addresses" etc.
1. FileMaker links in the relationship graph
In FileMaker, relationships can be defined in different ways. The most common types of relationships are equality relationships (e.g. =) and inequality relationships (e.g. >= or <).
Example 1: Equality relationship
- Relationship Customer addresses: This relationship is based on the equality of key fields in the two tables. This looks as follows in the relationship graph:
FileMaker relationship:- Customer::_pk_Customer_ID = Addresses::_fk_Customer_ID
This is a simple 1:n-Relationship (a customer can have multiple addresses). The relationship is represented in the FileMaker relationship graph by a line with a
=-symbol is displayed. In SQL, this corresponds to a SQL example:SELECT *
FROM addresses
WHERE
_fk_Customer_ID = Customer::_pk_Customer_ID
This displays all customers and their assigned addresses based on the matching customer ID. - Customer::_pk_Customer_ID = Addresses::_fk_Customer_ID
Example 2: Inequality relationship
- Relationship Customer actions via resubmission: This relationship not only uses equality, but also a comparison condition
>=. This looks as follows in the relationship graph:FileMaker relationship:
- Customer::_pk_Customer_ID = Actions::_fk_Customer_ID
- Customers::_Date_Today >= Actions::Date_Resubmission
In this case, an additional condition is added to ensure that only actions for which the resubmission date is before or equal to the current date are displayed. This type of relationship is indicated by a line in the relationship graph with a
>=-symbol. In SQL, this would be displayed with a WHERE-clause is expressed with two parameters:SQL example:
SELECT *
FROM Actions
WHERE
_fk_Kunden_ID = Kunden::_pk_Kunden_ID AND
Date_Resubmission >= _Date_TodayThis shows all actions that are linked to a customer and for which the resubmission date is today or in the past.
2. Links in the SQL context
In SQL, relationships have more flexibility in the use of relational operators. In contrast to FileMaker, where the relationships are visually displayed and predefined in the relationship graph, SQL allows you to use different operators within the JOIN- or WHERE-clauses.
SQL queries are created in FileMaker with the command
SQLAusführen ( sqlAbfrage ; field separator ; row separator {; Argumente... } )
is executed. The SQL query itself is located in the first parameter of the command within quotation marks. For this reason, all quotation marks must be escaped with a backslash (and the backslash itself), which should be inside your SQL query. Text keys must be enclosed with a single apostrophe, number keys must not.
Example:
SQLAusführen ("SELECT \"Name\" FROM \"addresses\" WHERE
\"_fk_Customer_ID\"='" & Customer::_pk_Customer_ID & "'"; ""; "")
Example: Comparative relationships
In SQL, you can not only use equality, but also inequalities in the JOIN conditions. These offer extended functionalities to filter data on the basis of time, quantity or status comparisons.
SQL Example of an inequality relationship:
SELECT *
FROM customers
INNER JOIN actions
ON Customers._pk_Customer_ID = Actions._fk_Customer_ID
AND Customer._Date_Today >= Actions.Date_Resubmission;
Only the data records for which the current date of the customer is greater than or equal to the resubmission date of the action are linked here.
3. Summary of the link types
- Equality relationships (
=)These are the standard method in FileMaker for linking data records. They are indicated in the relationship graph by a line with the=-symbol and correspond in SQL to a INNER JOIN. - Inequality relationships (
>=,<=)These extend the functionality and allow you to create links based on comparisons. In the relationship graph, they are indicated by a line with the corresponding comparison symbol. In SQL they are displayed via JOIN-conditions or WHERE-clauses were realized.
Layout design
Layouts in FileMaker determine how data is displayed and entered. A well-designed layout improves usability and ensures that data is captured efficiently and accurately.
Layout mode
Layout mode in FileMaker is the area in which you design the user interface of your database. Here you can place fields, add labels, create buttons and define the general appearance of the database.
- Add fields and objectsYou can drag and drop fields from your table into the layout. You can also add objects such as buttons, text fields and graphics to improve the user experience.
- Themes and stylesFileMaker offers predefined themes and styles that standardize the appearance of your layouts and make them more professional. You can customize these or create your own styles to personalize the design of your database.
- Navigation and user-friendlinessTo increase usability, you should include navigation buttons that allow users to quickly switch between different layouts. Clear and intuitive navigation is crucial to maximize user efficiency.
Scripting and automation
FileMaker provides a powerful scripting engine that allows you to automate tasks and create customized workflows. Scripts can automate simple tasks such as creating new records or control complex processes that involve multiple steps.
Scripting basics
Scripts consist of a series of instructions that are executed in a specific order. They can be used to manipulate data, generate reports or control user interaction with the database.
- Creating a simple scriptTo create a script, open the script workspace and click on "New script". Give the script a name and then add the desired steps. For example, you could create a script that automatically creates a new data record and forwards the user to the input screen.
- Conditions and loopsFileMaker supports the use of conditions (if statements) and loops to control the execution of scripts. These functions make it possible to integrate complex logic into your scripts.
- Error handlingIt is important to implement error handling routines in your scripts to ensure that unexpected situations are handled correctly. This can be done by using "If" statements that react to certain error codes.
Automation of work processes
Automating workflows in FileMaker improves efficiency and reduces the need for manual intervention.
- TriggerIn FileMaker, you can use triggers to automatically fire scripts when certain events occur, such as opening a layout or changing a record.
- Time-controlled scriptsYou can configure scripts to run automatically at specific times, which is particularly useful for regular tasks such as creating backups or sending reports.
Security measures
The security of your data is of the utmost importance in a database. FileMaker offers extensive security features to control access to sensitive data and ensure that only authorized users have access to certain functions.
User accounts and access rights
In FileMaker, you can create user accounts and assign them specific privileges. This allows you to control access to certain tables, layouts or scripts.
- Creating user accountsYou can create new user accounts in the "Manage security" dialog box. Here you can also define which authorizations each user has.
- Role-based securityFileMaker supports role-based security models where users are granted certain privileges based on their role in the organization. For example, an administrator might have full access to the database, while a data entry user might only be allowed to enter data but not delete it.
Encryption
FileMaker offers the option of encrypting databases both at rest and during transfer. This protects your data from unauthorized access, especially if the database is hosted on a server or transferred over the Internet.
- Encryption at restThis encryption protects the database file on the hard disk. Even if the file is stolen, it cannot be opened without the corresponding password.
- SSL encryptionIf your database is hosted on FileMaker Server, you can use SSL encryption to secure the data transfer between the server and the clients.
Best practices for database development
Developing a FileMaker database requires careful planning and a structured approach. Here are some best practices to help you create a robust and scalable database.
Testing the database
Testing your database should be carried out at various stages of development to ensure that all functions work as expected and that no unexpected problems occur.
Functional tests
Functional testing is the first step in ensuring that all components of your database work as intended. This includes testing data input, data processing and data display.
- Checking the field functionsTest all fields to ensure that they accept the correct data types and store the data correctly. Make sure that validation rules such as mandatory fields or specific formats (e.g. email addresses) work correctly.
- Script testsRun all scripts in your database to ensure that they run without errors. Pay attention to possible loops that are not terminated correctly or conditional statements that do not deliver the expected results.
- Layout testsCheck all layouts for functionality. Test that all buttons, links and interactive elements work properly. Make sure that layouts look good and are user-friendly on different screen sizes and devices.
User tests
User tests help to evaluate the user-friendliness and efficiency of the database from the end user's perspective.
- Pilot operationLet a small group of users use the database under real conditions. Gather feedback on problems they encounter while using it and observe how they interact with the user interface.
- Usability testsPerform formal usability tests in which users have to complete certain tasks in the database. These tests help to identify potential user problems and provide valuable insights into the usability of your solution.
Security tests
Security checks are necessary to ensure that your database is protected against unauthorized access.
- Access testsCheck whether the access rights are set up correctly. Test the database with different user accounts to ensure that only authorized users can access sensitive data.
- Penetration testsIf possible, carry out penetration tests to check the robustness of your security measures. This can help to identify vulnerabilities that could potentially be exploited.
gFM-Business Open Source FileMaker Basis-ERP
The software for the crash course
Download for free
Optimization of the database
After testing, you should optimize your database to maximize performance and improve the user experience.
Performance optimization
Performance optimization includes measures to improve the speed and efficiency of the database.
- Indexing of fieldsUse indexing to increase the query speed for frequently used fields. This can significantly improve performance, especially for large databases.
- Reduction of redundanciesEnsure that your database is normalized as much as possible to minimize redundant data. This reduces the amount of data that needs to be managed and improves overall performance.
- Optimization of scriptsCheck your scripts for unnecessary loops or complex conditions that increase the execution time. Customize the scripts to make them as efficient as possible.
Layout optimization
Optimizing layouts is important to improve usability and ensure that the database works well on different devices.
- Simplification of the user interfaceConsider whether the user interface can be simplified by removing unnecessary elements and making navigation clearer. A simple, intuitive user interface improves the user experience.
- Adaptation for mobile devicesIf your database is used on mobile devices, make sure that the layouts are optimized for smaller screens. Use responsive designs or create special layouts for mobile devices.
Maintenance and continuous improvement
The development of a database is a continuous process. It is important to regularly review and adapt it to ensure that the database meets current requirements.
- Regular updatesMake sure that your database and the plugins you use are updated regularly to benefit from new functions and security updates.
- User feedbackCollect continuous feedback from users to identify problems and find opportunities for improvement. Regular adjustments based on this feedback can significantly increase efficiency and user satisfaction.
- Backup strategiesImplement a robust backup strategy to ensure that your database can be restored at any time. Automated backups that are performed regularly are essential to prevent data loss.
Frequently asked questions about FileMaker database development
- What is a database and what is it used for?
- A database is an organized collection of data that is structured in such a way that it can be easily retrieved, managed and updated. It is used to systematically store information and enable access to this data, whether for reports, analyses or application processes.
- What is the difference between a relational and a non-relational database?
- A relational database organizes data into tables that are related to each other by using common keys. In non-relational databases (NoSQL), data is often stored as documents, key-value pairs or graphs, which makes them more flexible for unstructured data.
- What are tables in FileMaker and how do they work?
- In FileMaker, tables are the core of every database. They store data records in structured fields. Each table represents a specific type of data, such as customers, orders or products. The data in the tables can be related to each other to create complex queries and reports.
- What is a primary key in FileMaker and why is it important?
- A primary key in FileMaker is a unique field (often an automatically generated ID) that identifies each record in a table. It is crucial for the unique identification of a data record and for the connection between different tables in order to define relationships.
- How do I create relationships between tables in FileMaker?
- In FileMaker, you can create relationships between tables in the relationships graph. You simply draw lines between fields that link the tables. Often a primary key from one table is joined to a foreign key in another table to define the relationship.
- What are fields in FileMaker and what types of fields are there?
- Fields in FileMaker store the data within a table. There are various field types, including text, number, date, time, container (for files and images) and calculation fields that display dynamic values based on other fields.
- What does normalization mean in a FileMaker database?
- Normalization is the process by which data is split into multiple, logically structured tables to avoid redundancy and ensure data consistency. In FileMaker, this process is accomplished by creating separate tables and defining relationships between them.
- Can I use SQL in FileMaker?
- Yes, FileMaker supports SQL via the ExecuteSQL function. This allows you to create queries to retrieve, filter and aggregate data from tables. This is particularly useful if you want to create complex queries or retrieve data from different tables at the same time.
- What is a foreign key in FileMaker and how is it used?
- A foreign key is a field that references the primary key of another table. In FileMaker, the foreign key is used to create relationships between different tables, such as between customers and orders. This allows you to link and query data in different tables.
- How do I set up data integrity in a FileMaker database?
- Data integrity in FileMaker can be ensured by using validation rules and relationships. For example, you can ensure that a field does not allow duplicate values, or define the relationship between tables so that invalid data (e.g. non-existent links) cannot be saved.
- How can I query data in FileMaker and create reports?
- In FileMaker, you can filter data using searches and queries. You can define criteria to display exactly the records that meet your requirements. Reports are created in FileMaker using layouts in which you can format and present the data in various ways, e.g. for print views or analyses.
- How important is the backup of my FileMaker database?
- Backups are essential to protect your data. FileMaker Server offers automatic backup options so that you can create regular backups of your database. This is especially important to avoid data loss due to errors or hardware failures.
- How can I manage user rights and access control in FileMaker?
- In FileMaker, you can define access rights for different users or user groups. You can use the Security function to define who can access which data and functions. You can control in detail whether users can view, edit or delete data, which increases data security.
