Jump to content

ERD analysis


This topic is 6592 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Team-Event.GIF

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Loops.GIF

Link to comment
Share on other sites

This topic is 6592 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.