Planning a relational database
Before you create a relational database using the relationships graph, design your database on paper or onscreen. In database terms, the plan that you develop is called an entity relationship diagram.
For the general steps for designing a FileMaker Pro custom app, see Creating a custom app.
To plan a relational database:
- Determine the categories of information that your relational database will need. These categories will be tables in FileMaker Pro.
For example, a Sales database might include these tables: Customers, which includes customer information; Invoices, which includes order information; and Products, which includes product information.
- Determine how the tables are related to each other. You can do this by writing simple sentences that describe how the categories interact, such as "customers order products" and "invoices track customers’ orders."
- Connect one table to another to indicate a relationship between them. For example, customers can have invoices and invoices can have products.
If a table has no relationship to another, it’s probably unnecessary. In this example, an Employees table doesn’t fit into this relational database.
- Indicate the type of relationship between tables by connecting them with a representative symbol.
- In a one-to-one relationship, one record in Table A matches one record in Table B.
- In a one-to-many relationship, one record in Table A matches many records in Table B.
- In a many-to-many relationship, many records in Table A match many records in Table B.
See About relationships.
- a customer can have many invoices
- a product can appear on many invoices
- an invoice can have many products
- Note that there is a many-to-many relationship between Invoices and Products. You cannot set up a many-to-many relationship directly between two tables.
Relational databases handle one-to-one and one-to-many relationships directly. You must resolve the many-to-many relationship by using an intermediate table, which breaks the many-to-many relationship into two one-to-many relationships. To solve the problem in this example, add the intermediate table Line Items to store information about products sold.
- a customer can have many invoices
- an invoice can have many line items
- a product may appear on many line items
- Determine the fields each table will need.
Each table has only one subject, and all fields in a table describe only that subject. For example, the fields in one record of the Customers table together store all the information about one customer.
For the same reason, you should assign each customer a unique, identifying number. In your database, this is a primary key. You wouldn’t enter a customer identification number into the table unless you had a new customer to add, so the existence of a customer number determines the existence of a record. A Customers table might also have fields for the customer’s name, address, and phone number.
A Products table might have fields for a product identification number, the unit price for each product, and the quantity in stock. A Line Items table might have fields for product and invoice identification numbers, the name, unit price, quantity, and total price of each product sold. An Invoices table might have fields for an invoice identification number, order date, and salesperson.
- Determine the primary key field (or fields for a multicriteria relationship) for each table, and indicate each one in your plan. Then indicate the foreign key field or fields in each table.
In this example:
- the primary keys are Customers::Customer ID, Invoices::Invoice ID, Products::Product ID, and Line Items::Item ID
- the foreign keys are Invoices::Customer ID and Line Items::Product ID
- For each table, decide which fields will store data and which fields will be used from other (related) tables.
Based on a table’s subject, you can see where it makes sense to store the data and where to use data from a related table. Other than key fields, all fields should appear only once in your database. Remove occurrences of fields that don’t pertain to the table’s subject.
- Connect each primary key to its corresponding foreign key in the related table.
What establishes a relationship between tables is that their key fields contain data that matches the criteria of the relationship.
- a customer can have many different invoices, but an individual invoice can have only one customer
- an invoice can have many line items, but an individual line item appears on one invoice
- a product can appear on many different line items, but an individual line item has only one product
This example shows:
After resolving the many-to-many relationship, this example shows:
To display customer data in the Invoices table, you must have a common field between the two tables to create a relationship. Customer ID is that common field. In the Customers table it is the primary key; in the Invoices table it is the foreign key.
In the Line Items table, Product ID is the common field between the Line Items and Products tables. In the Product ID table, this field is the primary key; in the Line Items table it is the foreign key.
These key fields are a type of match field. See About relationships.
This plan now indicates: