Designing your data model
in this video you'll create the foundation for your
clinics custom app by learning about relationships
you'll also create an entity relationship diagram or erd while
planning your custom app you created the following goal statement design a mobile
app for therapists to record services delivered to clients during their visit
and based on that statement and the needs of your clinic you identified
three tables of data client visit and service
to identify how these tables relate to one another start by looking at the
client and visit tables one client can have multiple visits over time but each
visit belongs to only one client this is called a one-to-many relationship when
creating your data model use a crows foot to represent a one-to-many
relationship one client many visits in order to relate these two tables each
visit needs to know which client it belongs to but using something like a
name or a combination of fields can be problematic what happens if there are
two Jane Smith's in the client table it becomes unclear which Jane Smith went in
for the recorded visit or what if Jane Smith changes her name to Jane Doe the
visits would no longer match up since the visit record still shows Jane Smith
as the client name and a misspelling in either table would also impact the
matches instead using a unique value that always exists and never changes
solves the problem when a unique value is used it's called
a primary key this means that the client table will only have one record that
identifies as client ID 101 who in this case is Jane Smith the client ID will be
unique and only assigned to Jane Smith if that value is referenced in another
table that's called a foreign key client ID 101 in the visit table is foreign
because it did not originate from the visit table it was unique to the client
table you'll see the client ID 101 repeats in the visit table to track
every instance the client visited the clinic as a best practice it's a good
idea to have a primary key in every table even if you don't expect to use
the primary key in a relationship this prepares your tables for potential
relationships as your custom app grows it can also be a great way to reference
any specific record during development and when troubleshooting another best
practice to help you identify when a foreign key is needed is when you see a
crows foot in your table relationship this is a good indicator that you'll use
a foreign key in that table similar logic can be applied to services a
service can be defined as the actions the therapists take during a specific
visit while services are tied to a client a client must come into the
clinic for a visit in order to get the services one visit can have multiple
services but each service belongs to one visit
just like client to visit the one side uses a primary key the visit and
services table are related by the visit ID a unique identifier for each visit
a client can have multiple visits and a visit can have multiple services by
drawing out your data model using tables and branching lines you created an
entity relationship diagram or an ER D an ER D will become the foundation for
building your custom app and FileMaker Pro while writing out a plan helps
describe the business problem drawing out an ER D helps to explain what tables
are necessary and how those tables will relate to one another in this video you
learned about the one-to-many relationships between clients and visits
and visits and services you also learn to create primary keys and foreign keys
in order to relate your data in the next video you'll launch FileMaker Pro and
use your er D to begin creating your apps tables fields and relationships