ERD Advice

[cellmaker]

ERD Confusion -

Can anyone provide helpful advice in untangling my brain?

I'm attempting to put together an ERD, but I'm starting to confuse data structure with how I wish to control the input of data.

Here are the basics, in plainish English:

An organization hosts different programs that involve school children (in teams) and volunteers (adults who take on different volunteer roles).

The programs are comprised of activity types (competitions, committees, mentoring, other events), and volunteer roles are defined in terms of these activities. So, you can have Program 1, Activity 1, Role 1; Program 2, Activity Type 1, Role 1; etc.

Teams can belong to only one program type.

Teams participate in competitions or other events, but not in committees.

Teams can have mentors, which are specified as a volunteer role type.

Teams have multiple members.

Volunteers can be involved in any program, in any role.

Volunteers can have multiple contact records.

Volunteers specify their preferences for volunteering.

Events are instances of a program activity. Teams enter events (except Committees).

Each event has specified volunteer roles, but these roles must fall into the correct categories. You cannot, for example, set up a role specific to team mentoring for a competition.

Volunteers are matched to Event Roles via their preferences. They are also matched to Teams for Mentoring (also via preferences).

Anyway, that was a lot of information. I've attempted to normalize the attached macrame-like ERD (pdf), but I'm not sure if it makes sense. I'm having a lot of difficulty keeping the data structure separate from the filtering information.

Would be nice to hear some opinions and questions, which would definitely help.

Thx.

ERD.pdf.zip

[Ender]

I've simplified the ERD a bit to make it easier for me to understand. The main difference in reading it is that foreign keys are implied by the chicken feet-in relationships. Slashes on the chicken feet mean there is a dependency on the related keys.

In my diagram I've removed the Volunteer entity and changed what a Contact record is by using new Phone and Address entities. To me this made more sense, but you may have good reasons for designing those the way you did.

Also I have left off the normalized Program Type, Activity Area, Vol Role, Country, State, City entities, though you could keep them if they are important.

Otherwise it seems to make sense to me.

[cellmaker]

Ender -

Thanks for taking a look. And yes, your version is much easier to decipher.

My actual ERD-alike in FM looks like a bunch of spaghetti at the moment, and interface issues have caused me to use all manner of weird relationships for the creation of related records, etc.

Is there an emerging "best practices" model for translating a standard ERD into FM-speak? I often find that, from an interface standpoint, I've backed myself into a cul-de-sac, or I'm having to create many separate "relation threads" in order to produce different kinds of reports. So in some ways FM7, though cool in concept, seems to be making things harder than they should be. Almost as if the new data structures are fighting against the automated features of FM that we've all gotten used to over the years.

Again, my thanks.

Thoughts?

[Ender]

Charlie,

The differences between the ERD and TOG are subtle and a bit confusing. The multiple table occurances provide very explicit relational paths--which is necessary.

I've always done my relational thinking in terms of ERDs and I haven't done enough in FM7 yet to convert my thinking in terms of TOGs. I don't think the TOG is meant to replace the ERD as a design tool; the ERD is simpler to read.

Also, I don't generally worry about trying to incorporate every relational thread on the ERD. I just make sure the primary relatioships are on it so the data flow can be understood.

[cellmaker]

Ender -

The funny thing about the whole TOG issue is that it's easy (at least for me) to lose track of what I think of as the true data structure. I'm almost tempted, in many cases, to set up the closest thing I can to a normalized ERD, then slip back and forth between the TOG path I'm using (often for navigation purposes -- so many globals) and the ERD version for relational and data integrity purposes/peace of mind.

I suppose some of this starts to suggest separation of data and function/interface, but I haven't quite gotten my head around how to do that properly yet.

c

[Fenton]

It's true that we're no longer in Kansas. The ERD is more or less the "logical" connection of the principal entities, whereas the TOG graph is the actual implementation. The TOG has one characteristic that makes it very different, which is that a single entity will often (usually) appear several times; but what's more unnerving is that some of those occurrences will be only tenuously connected to, or even completely unconnected from, other TO's of the same entity.

This is sometimes an enforced behavior, to avoid circular references. What it means though is that you can't necessarily reference a TO that you would think is logically connected; only if it's actually connected on the graph. You can't always go by the name (which can't always show all its connections), you have to follow the lines.

The result is that once you go off in one direction, you may not be able to access what you think of as the "main" TO for an entity. I find myself creating TO's, little duplicates of the main entity, at the ends of lines, just so I can read data, to show in portals, etc.. I think this is to be expected.

It seems like the TO's will tend to be organized in groups, a group tends to be for a general module of operation. For example, if you have 2 tables which normally go through a 3rd central table, but are now running several operations directly between each other, with global fields also involved of to the side, etc., then you may find yourself with a new little group of TO's. The colors help to keep the entities identified.

Eventually I imagine we'll get a good sense of exactly when this is needed. On a positive note, sometimes following the lines shows you that you can easily get data further up/down the line that would have been a real pain in version 6. I wish I could be more clear on all this, but it's still early days.

[cellmaker]

Indeed.

Having 2 (or more) TOs for the same table when you're really just trying to put together an ERD is a bit confusing. Coloring helps, a bit.

It would be great if the TOG could be sectioned off into component parts/groups, with the ability to comment things. In fact, being able to put some explanatory text, boxes, etc., on the TOG would be most helpful.

Anyway, I'm going to try to get much more modular in the way I put these things together. I define my tables and fields (the ERD) pretty much by internal autopilot, but when I get to the implementation stage, my TOG starts to look absurdly unlike what the ERD should dictate. I just spent about a month on a thought piece that finally crashed down around me because the interface-driven TOG had overwhelmed the logic of the solution.

c

[Fenton]

I would agree that the length of the names, with no other way to comment what a TO is for, can make things a bit confusing. If you give the TO a complete name, i.e., the name of the Table it references, the source field and the target field, it's kind of long. And even then it's sometime unclear from the name whether the path from where you are will end up with what you want (but usually it does).

I find it handy to take a quick screenshot(s) of the relationship graph and open it in Preview. Then I can reference it quickly to look at the TO's and lines, from wherever I am in FileMaker. If I change it, I take another SS and toss the previous.

As far as Comments, yeah that would be nice. But I'm not sure where they'd put 'em. My suggestion would be to include a Comments field in the relationship dialog. And perhaps another available for the TO itself, visible with a right-click (contexual menu) on the TO (no right-click yet). That should cover it -)

[cellmaker]

Thanks for the idea on the screenshot. I'm going to try it out.

As for commenting, your ideas are all good. Let's hope FM is thinking about these things as much as we are (hehe).

Thanks for your help.