Complex Relationship Question

[niko]

Here goes my first FM Forums post. Please bear with me.

 

I am working for an organization that is probably not very unique in its needs, but I am a bit of a meticulous perfectionist, and I can't find the absolute best way to scheme this relationship.

 

Our organization has families, individuals and students who participate in programs. Some programs are free and others cost money. Our programs are occasionally co-sponsored by outside organizations.

 

Additionally, we're a non-profit that accepts donations and grants from organizations, parishes, and individual/family donors. 

 

We want to track information about our program participants. Specifically, we want to track the organizations and parishes that they're involved in.

 

My initial structure went a little like this:

 

people--<registrations>--programs

people--<peopledonations>--donations

people--<peopleorganizations>--organizations

people--<peopleparishes>--parishes

 

In this case, everything runs through people. However, if an organzation makes a donation directly to us, we have no way to mark this without running it through a person. I've attached the relationship diagram for our database so you can see what I mean.

 

Is there a better way to be doing this? Is there a way I can draw a secondary relationship to the donors table directly from the organizations or parish tables?

 

Thanks for your help!

 

- Niko

ovm_database.pdf

[Lee Smith]

Have you looked at this file that is provided by FileMaker Inc? https://store.filemaker.com/US/ENG/EDU/entry/form/

[niko]

Lee,

 

All I get when following that link is the FM Non-profit store page.

 

- Niko

[Lee Smith]

Ah man, they moved it.

 

I did a search  in Google for donations and it returned several hits, of which, this link was included. Donation

[eos]

Is there a way I can draw a secondary relationship to the donors table directly from the organizations or parish tables?

 

Your question suggests that you probably should read this …

 

http://fmhelp.filemaker.com/fmphelp_12/en/html/relational.11.1.html#1027502

 

… and/or study some other introductory material on using the relational concepts and features of FileMaker. 

 

This will help you to understand what the following two screenshots of a mock-up are showing.

 

 

 

As you can see, organizations and parishes are simply organizations with different categories; donations can be made by persons or organizations, by virtue of using a solution-wide unique ID for persons and organizations. Using multiple TOs of the same table allows you to use the same table in different relationships/roles and create core groups of activity or priority.

Ah man, they moved it.

 

I did a search  in Google for donations and it returned several hits, of which, this link was included. Donation

 

Lee, your link leads to a Bento template 

[Lee Smith]

S h i t. then I recommend that they follow your advise, and do the google search for additional information.

[eos]

S h i t.

 

… happens! 

 

<earnestness>You're doing a great job!</earnestness>

[Fenton]

[ Actually writen earlier, but posted after breakfast. eos and I are seing much the same thing :-]

 

Basically, you'll need some more for your Relationship Graph. Each type of "group or person" who can give a "donation" will need a relationship to that table.

 

I would change what you have into separate "table occurrences groups" (short "TOG", called the "Anchor Buoy method"). Because otherwise the relationships will get too hard to deal with (for most of us).

 

Though yours is all in the same "table occurrence group" now, you've put them in place is a first beginning of the method. The first "line" of your Relationship Graph is what the "People" TOG would keep. It would also keep the line down, to Donations. 

 

Organizations would get moved to a new TOG, "ORG__Organizations". Its connection to the PE__People (naming convention for current "people" table occurrence (TO) table would be unlocked, and a new (duplicated) "people" would be connected to ORG__Organizations (so you can see there people more directly), and them the OrganizationInvolve…(?). 

 

I don't know whether "parish" belongs directly to "PE__People", or whether parish should be its own TOG, as I don't know if there are single people who are neither "parish" or "organizations" (which would mean "yes", make "parish a separate TOG). 

 

This may sound a long trouble to do. But, I think you've find it is not really. It is the best way to handle a more complex database; and yours will get more complex (as you can see happening already).

 

I have worked on such a complex database. But it was years ago. It is even multiple files (like 40 files). It is too much for even me to understand now, and certainly not how I'd build it again. So it's not of much use. But the general idea above is what I think you should do. 

 

If you can make a clone of your file, I could do the above fairly quickly to show you what it would look like. There are a few more changes, for layouts perhaps. The sooner you make the change, the better.

[Lee Smith]

Final, Final. Link

[niko]

Thank you all for your help! Fenton, this database is still in relationship development and not in active use, so I don't have to worry about timing too much. I'm going to do the reading you guys have given me, and I'll be sure to get back to you if I have any questions.

[Fenton]

Feel free to upload a saved simple (dummy) or Clone (empty) of your file (if you wish), if you have any problems or question about how to setup the Relationship Graph (table occurrence groups); also an optional method to name them, so they sort by a list (which will be needed eventually).

 

Personally, I have retired, no longer an actual developer, and do these posts and tweaks as a hobby. 

[niko]

Thank you all so much for your help.

 

Fenton and EOS, I have been doing some reading on the A/B method, and I understand what you're saying now.