Relational Database Design ( A FileMaker Primer)

From Dwayne Wright PMP, PMI-ACP, CSM
Certified FileMaker Developer

WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

Databases, which some may argue are living entities themselves, generally are the answer to our information organization woes. Although if you ever endured a database switch over in the work place, you may disagree. How about if we say a well thought out, designed and executed database is the answer to organizing information? That sounds more on target, doesn't it?

Databases are quickly becoming the lifeblood of companies of all sizes. Normally, databases can be lumped into two categories. The interactive database which gets new information all the time and is used to perform business operations. The other is the warehouse or archive database that is used for research and trend analysis.

Relational databases are used to record and retrieve business information quickly and accurately. There are many things that will help a database be quick and accurate ... and ... there are many things that will prevent it. The fundamentals include useful interface design, accurate calculations, the productive use of scripting and the artful implementation of relationship design.

Databases can designed by a developer, by a team of users, a project manager or better yet, a combination thereof. Depending on how complex the project, you may need a developer, a project manager and a team member for the various departments that will be using the database. There are so many different tools, methods and practices to creating a database solutions, that it can look more like a piece of artwork than an information tool. I’ve seen some folks actually cheer when a new technique is shown at the annual FileMaker Developers Conference.

Relational database programs are commonly referred to as RDBMS or a Relational Database Management Systems. Common ones are SQL, Oracle and Access. FileMaker, although relational in many ways, is still not considered as a common RDBMS by most database professionals. Many times this is because FileMaker does not separate where data is stored and where the structure of the file is stored. By far, the most common relational database is SQL. This is not a database program but a language for working with data.

Relational databases ( like the author of this guide ) was a product of the 1960’s. So you can consider the idea of a relational database as a baby boomer. It was inspired by an IBM employee, Dr. E. F. Codd. He was looking for alternative ways to store and retrieve large pieces of information. He was a mathematician and so you can understand why relational theory is built upon a foundation of mathematics. Databases before relational theory were very hierarchical. To get any information, you always needed to start at the beginning ( normally called the root ) and work your way from there.

This wasn't so bad until you started getting a lot of data. Then starting at the root and going up the tree ( checking each branch as you went ) became unusable. In Dr. Codd's theory, data is stored in relations ( FileMaker calls these files or tables ) and is further broken down into tuples (FileMaker calls these records or rows ) and attributes ( FileMaker calls these fields ).

From here on, we are going to speak of these elements in common FileMaker terms.

Each record in each table needs to have one field that holds a unique piece of data for each record. In most FileMaker files, you would call this the Record ID field. Examples could be the customer id, the invoice id, the product id and so on.

In the last 10 to 15 years, more emphasis has been on the network. A common implementation of a networked RDBMS is called the client / server setup. Here data is stored on a single desktop computer and users/clients interact with the information via a network connection and a local application on their machine.

=
More info about the author and FileMaker in general, contact me at info@dwaynewright.com.

© 2007 - Dwayne Wright - dwaynewright.com

The material on this document is offered AS IS. There is NO REPRESENTATION OR WARRANTY, expressed or implied, nor does any other contributor to this document. WARRANTIES OF MERCHANT ABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE EXPRESSLY DISCLAIMED. Consequential and incidental damages are expressly excluded. FileMaker Pro is the registered trademark of FileMaker Inc.