A Database of Your Friends - File Maker

If you are keeping track of your friends on a personal database at home (so you never again forget to send them birthday gifts) you might need only one database for all of the information about each friend. You would probably need fields for name, address, phone number, email address, birth date, and so on.

However, if you wanted to start tracking related lists of information for each of these people, like their hobbies, their children (and their birthdays), their favorite books, or any other related list of information associated with each person, you would probably set up a related database.

Alternatives to a Relational Database
Actually, you could do one of several things instead of using a relational model. Let’s examine the possible scenarios using the situation where you wanted to keep track of all of the birthdays of your friends’ children:

Scenario 1: Create a field in Friends.fp5 called Children and enter the names of everyone’s child in this field, followed by carriage returns. You would probably make this field appear as a multiline field on screen to accommodate all those carriage returns and add a scroll bar on the Children field so that you could scroll through the contents of the field if it didn’t entirely fit on screen.

Alternatives to a Relational Database

Scenario 2: Create a field in Friends.fp5 for each child of every friend. You might guess that none of your friends would ever have more than six children and create Child1 through Child6 fields. You might then stack these fields on screen and enter each child’s name for each friend in the appropriate field.

A Database of Your Friends

Scenario 3: You might also create a related database called Children.fp5 with a Child and perhaps a Notes field in it, then add a portal to this database on the screen in the Friends.fp5 database . For every child of each friend you would add a line in this portal which, in turn, would create a related child record in the Children.fp5 database, which would always show up in that friend’s Children.fp5 portal.

A Database of Your Friends

So what are the advantages of using the related database method versus the flat-database method, the method described in scenarios one and two, where all of the data in your database system is entered into only one database? First let’s learn about the disadvantages of the flat-database method.

Disadvantages of Scenario 1
In scenario 1, you have to lump all kinds of different data into one field, including a child’s first name, last name, middle name, address, phone number, and so on. This is bad design that makes it almost impossible to automatically sort the children alphabetically in a list.

It is impossible to do a lot of things, actually, with this sort of scenario. For example, to alphabetize these fields you would have to go into this field and manually cut and paste each name in order. Also, this design doesn’t force you to enter your data in any standard way by requiring data like first and last name be put into specific, separate fields. In fact, the Children field is completely freeform: You could enter last name/first name one time and first name/last name another. The field doesn’t care.

Too, FileMaker limits text fields to only 64,000 characters, so if you were tracking something more verbose than children’s names (like a log of every time you telephoned someone) you would be in trouble after a while because you’d run out of entry space.

Disadvantages of Scenario 2
Scenario 2 is a little different in that the data for each child is separated out a bit more in that each child is entered into a separate field. You could even add a Child1LastName field and split out the children’s first and last names into separate fields to standardize data entry. But, again, you could never automatically alphabetize the names in a list or report, and a user could technically fill in the Child3 field and leave the Child1 and Child2 fields blank. And, for every friend that has only one child, you must always see five other blank fields on the screen that will never be filled in. This is a waste of valuable screen real estate.

But by far the biggest problem with scenario 2 would be if you acquire a friend with seven children. What would happen then? This scenario would leave you no place to enter information on this seventh child and would force you to add a Child7 field to the Friends.fp5 database.

While this might not seem like a big deal with this simple single-user system, consider an online store like Amazon.com. In a more complex system like this, any given customer’s order might have one item on it — or 200 items! And what if for each item on the order you wanted to include four bits of data about the item, tracked as it is added to the order (for example, ItemID, ItemName, ItemDescription, and Price) so that the order can be properly invoiced and packed at the Amazon.com warehouse?

If someone were to order 200 items at Amazon.com and Amazon.com were to use scenario 2, Amazon.com’s Orders.fp5 database would need 800 fields just to track all the information on the ordered items for each order. And then what if someone were to order 201 items?! Start passing out the Excedrin Migraine.

The Solution: Scenario 3
Enter the relational database model. With this model, you simply set up a relationship from the Friends.fp5 database to the Children.fp5 database and put a portal to this database on a layout in FileMaker. Now, whenever you want to enter a child for one of your friends, you just scroll to the next line in the portal and type in the child’s information. No matter how many children any friend has, whether they have one or twelve, there’s always another portal row to enter the name into.

In the relational model, Amazon.com can have a related database for all the items on a given order. When someone clicks the order button on an item, the database simply “goes to last portal row” and enters the item on the order. It doesn’t matter how many times the user clicks. The related database never runs out of room in a field as it would in scenario 1, and it never runs out of fields as it would in scenario 2.

Another good reason to use the relational model is because it eliminates the need for duplicate or redundant data. On a flat database Amazon.com invoice, every time a user wants to order something they’d have to reenter all their contact information first, which would be a big turn-off. But if a customer has a unique customer ID and creates an order, all of that customer info can be “looked up” automatically, via a relationship and placed on the order, forever preserving the contact info just as it was when they submitted the order.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

File Maker Topics