Relational Database Design 101:
Relating A Single Database to Multiple Databases

The following article is reprinted from the pages of FileMaker TechInfo Knowledgebase.
The article is reprinted in its entirety, and Revelation Enterprises makes no warranties,
whether express or implied, as to the validity of the content of this article.

This article may be located in FileMaker TechInfo at this address:

http://www.filemaker.com/ti/104073.html

Entity Relationship Model

Designing a relational database takes a lot of planning up front before you even create your first file and define your first field. Planning ahead will save time and effort later on and will result in fewer changes to be made later.

It is helpful to begin the database design process on paper first. This will help you think through the structure of your database. The more thoroughly you can envision the entities and their relationships, the more coherently you can later design the file structure for your database. The diagram that you will develop is referred to as the entity relationship diagram.

The first step in creating an entity relationship diagram is to decide which categories, or entities, of information you’ll be working with. Keeping with our invoice scenario, are the entities products, vendors, salespeople, employees, customers, invoices, all of these or a combination?

Once you have identified the entities, you then identify the relationships between the entities. Which entities are related to other entities?

Take a few minutes and on a piece of paper write down the entities you might need to develop a database for a small business that sells a product. After writing down the entities, draw lines from one entity to another to indicate a relationship between the entities.

Your diagram should look something like the one in figure 3. You may have more or fewer entities than the ones shown or entities with different names. In the example shown, customers can have invoices and invoices can have products.

Admittedly, this is a very simple database structure. But it will serve as a good example of database design.

If you find an entity that has no relationship to any other entity, it’s a good sign that, perhaps, that entity is unnecessary. However, for now, leave it as part of your diagram.

Once you have drawn the entities and indicated the relationships between the entities, you will next look at the type of relationship.

Remember that relational databases handle one-to-one and one-to-many relationships directly and the many-to-many relationship must be resolved by the use of an intermediate file. This intermediate file breaks the many-to-many relationship into two one-to-many relationships

Next, you will indicate the type of relationship between the entities by drawing a symbol from those pictured in figure 4. For example, if it’s a one-to-many relationship, you would use the one-to-many symbol.
(See figure 5)

In the previous example (figure 5), one record in File A can have many occurrences in File B, one-to-many.

Remember, in a one-to-one relationship, one record in file A matches one record in file B. In a one-to-many relationship, one record in file A matches many records in file B. In a many-to-many relationship, many records in file A match many records in file B.

Look at your diagram and draw the appropriate symbol for the relationships you specified.

After you have drawn the symbols indicating the relationship types, your diagram may look like the one in figure 6.

The diagram in figure 6 shows the following:
A customer can have many invoices.
A product can appear on many invoices.
An invoice can have many products.
Employees didn’t fit.

The diagram in figure 6 also indicates that there is a many-to-many relationship between Invoices and Products. You cannot set up a many-to-many relationship directly between two files.

For example, if you create a relationship between Invoices and Products based in a field named Invoice ID, each product can appear on only one order. If you create a relationship based on a field named Product ID, each invoice can have only one product.