ERD analysis

[Rainy Pass]

Can anyone give me any pointers for analyzing an ERD for problems? I understand how to put together simple diagrams, but how do I spot problems in a complex business solution. How do you test the ERD on paper before you start building the database? I have read what there is in Using FM 7, as well as a couple of white papers, but I am still having trouble. Any help would be appreciated.

[Ender]

First, it's important to remember that an ERD (Entity-Relationship Diagram) is not the same as a TOG (Table Occurence Graph) that FileMaker uses. They start out looking the same, but because we can't have loops in a TOG, we instead create additional Table Occurences.

That said, I always begin my projects with an ERD. Although ERDs can have different styles, depending on how you learned to make them, the idea is the same. An ERD puts fields into logical entities, and show how those entities relate. The basic question that you must ask about each field is "could there be more than one of these for this entity?" If so, you might need to break it off into a separate related table. Often our initial entities end up spawning several related tables.

When possible, I try to share my ERD with the intended users. After showing them what the boxes and lines mean, they can start to visualize what's going on, and help contribute and catch problems.

The techniques for creating ERDs are not FileMaker specific. You can find general information about ERDs and data modeling on the Internet or in books about database design.

Translating the ERD into a TOG is the next step. This can be a bit tricky with ERDs that have loops. I have not yet had to translate a large, complex ERD into a TOG of a single FM7 file, but I imagine it will be quite a lot of work. There have been a few discussions on how best to do this (by using unconnected TO groups or having everything connected.)

Anyway, if you have specific things that are giving you trouble, let use know.

[Rainy Pass]

I like your user name. Card rates very high on my list of great writers.

I have not concerned myself with the translating from ERD to the TOG. At least in any conscious way, but maybe that is why I am getting confused.

I am the client for this project. The database is to be build for a company I developed, the business rules have either been imposed by our customers or directly by me. There is no question as to what needs to happen, and I can change some of the rules if I deem it necessary and am willing to change our system to fit the data design (not my first choice).

After you have done the entities, relationships, and optionalities

[Ender]

Ho Rainy Pass!

Well, loops are often present in ERDs (see attached example.) As far as evaluating the ERD for problems, you have to double check every relationship to make sure the one-to-manys are in the correct direction. These get flipped around when you add join tables, so it's sometimes confusing.

Then, it's best to get rid of the many-to-manys, and consolidate the one-to-ones. These types of relationships are rarely needed, and should be used with only careful and deliberate thought.

Finally, I would say that the ERD need not cover every relationship your solution is going to need. Just diagram the primary relationships; mostly the ones that are used by the data. Relationships that are used just for interface do not usually need to be in the ERD.

[Rainy Pass]

Thanks for the example. It helps.

In a recent class I was cautioned against loops and, having more than one table with two or more Many relationships. What would be the rational for this? Your example clearly has loops and multiple low points.

[Ender]

I'm not sure what the rationale is behind your class's caution. Loops will exist if the relationships demand them. See the attached diagrams for some clear examples of this. In the first case, a self-join is used to specify a simple supervisor-supervisee relationship for an Employee DB. In the second case, a company has multiple sites, and it's important to specify the location each employee happens to be assigned to. There are other examples, usually involving more tables.

I also don't see a problem with having multiple tables with more than one Many relationship. A table that is on the receiving end of two many-relationships is usually a join table (when the table is dependant on both parent tables.) Join tables happen all over the place. Join tables can also be related to other tables for various reasons. There may be something else looking into the join table or the join table may be looking up something from somewhere else.

[Rainy Pass]

Thanks for your help. Apparently I was trying to constrain the process overly much. I have started a test DB to help work out my minor problems and questions. Thanks again.