Database Reference
In-Depth Information
you apply $80 to the first invoice and $20 to the second. This dollar amount applied to each
invoice can't be stored in the Invoice table because an invoice can have several payments. It
can't be stored in the Payment table because you have
two
amounts and only one payment
record. The best place for it is right in the join table itself.
By adding a join table between Invoices and Payments, you've fleshed out the many-to-
many relationship on your ER diagram. Now you know which foreign keys you need to
make it all work. You're finally ready to build your database.
Figure 5-11. This database (it's not the one you're building here) tracks orders for the products
you sell. Since a product can be ordered many times, and an order can include many products, the
Orders and Products tables have a many-to-many relationship. A Line Items table connects them. It
records which products were on each order and also shows quantity and price. Here, a line item is
a real entity, and the Line Items table also serves as a join table.
Creating a Relational Database
The planning is finally over. Now comes the fun part—actually making the database. And
believe it or not, this part will go
much
more smoothly with your plan in place. You create a
relational database in three steps. First, you tell FileMaker what tables you want and then
you add the fields to each table. Finally, you add relationships.
