Although I can't possibly hope to cover the field of database design in a single article I'll cover the more salient points as regards FileMaker Pro.

Terminology

The basic terminology is:

Field - A slot for holding data. In FileMaker Pro this can be a text string, a number, a time, a date or a calculation based on other fields. An example might be a text field which holds someone's name.

Record - A collection of fields which represents a complete data set for a particular item. For example a contact database would have a name, address, telephone number, etc.

Layout - More commonly known as a table to users of other database products, this is a subset of the fields in a record grouped together in a logical way. For example, a letters database could have two layouts - one for inputting the body of the letter with another layout for entering the address label. Although it looks as though you entering data into two separate areas, the data is still contained within a single record.

There are two options for storing data within database:

Indexed - Here the data is stored and an index entry is created allowing rapid retrieval of the data at the expense of memory and disk space.

Unstored - Generally the results of calculations are unstored and recalculated every time the record in question is viewed.

Defining Relationships

FileMaker Pro is fully relational and this means that data only needs to be stored in one location and referred to from other databases. The way that this works is a field within a database is defined as a 'match' field with a field from another database. When the data in the two fields corresponds then any data within the record in the second database can be accessed in the first.

FileMaker Pro allows three types of relationship - one to one, one to many and many to many although the last is not entirely apparent from the documentation.

Useful one to one relationships are pretty rare and generally if the data is only ever going to be used in one record then it's better to keep that data within the single record for speed reasons.

One to many is the most common of the relationships and is used in two different ways within FileMaker Pro. There is the common data method where multiple record share a common value. For example a contact database solution might use a separate contact and company database as you might have multiple contacts at one company. This way if the company address is changed then only one record needs updating.

Alternatively there is the single parent method where an area know as a portal lists records matched to the current records. An example of this would be a job cost tracking database where individual cost items are assigned a job code and job sheet record compiles this as a list which can be seen at glance.

Many to many is rarer and can lead to a significant slow down if not carefully used. In FileMaker Pro data separated by carriage returns are matched separately rather than as a whole. This could be used in a diary database where appointments for a week could be displayed in a portal by matching a list of days in the week against the appointments database.

Key Fields

When using a database as a backend for something like a chat system or online commerce then you'll need some way of storing a users current options. For this you need a unique key field which identifies the user within the database solution. There are two ways to handle this within FileMaker Pro - either a unique serial number can be generated every time a new record is created which is probably the best solution for a online commerce system or you can use data validation to ensuring that all entries within a field are unique, which is probably better for a system where the key field needs to be something resembling a name such as a chat system.

Auto Enter Data

When ever possible you should auto enter data into fields based on earlier entries. Above where I talked about a contact database which had a separate companies database then after entering the company name the database should automatically copy in the rest of the company details without any further assistance from the user.

This is especially important for situations where data is being submitted via a URL as this reduces the length of the URL which helps for greater compatibility.

Further Info

I hope this small introduction to database design has helped those of you who are novices in this area. If you want further info on designing databases then a good jumping off point is at ClickWorld for all things FileMaker Pro.

Alternatively there are a number of good books available - try searching Amazon for FileMaker Pro.

If you want to jump right in there are two general FileMaker Pro mailing lists:

FMPro - This is hosted by BlueWorld. To subscribe send a message to:
fmpro@blueworld.com with subscribe in the subject heading

FMPro-L - This is hosted at Dartmouth. To subscribe send a message to:
listserv@dartmouth.edu with subscribe FMPro-L in the body of the message

Be aware that both of these list are very heavy traffic.

Last but not least is the FileMaker Pro home page, but this will probably be undergoing reconstruction at some point in the near future as Claris metamorphoses into FileMaker, Inc.