First things first. Some of your questions about when to create new table occurrences and when not to—there's rarely a single best answer, but there are some recommended approaches—may be addressed in Ray Cologon's excellent "Approaches to Graph Modeling: 9 Steps Toward Enlightenment With FileMaker Pro" white paper linked here.
I know you've asked more questions than that, but wanted to get that in your hands first.
That out of the way, why do you want different layouts for different subsets of your Contact table, if I may ask? Is it because you need to show different fields for, say, a volunteer vs a refugee? If not, then you can usually use one layout and filter it to show different contacts, by role, on the fly. In this case, you'd obviously only need one TO for that layout, but even if you do stick with needing different layouts for different contact roles, you still would, in most cases, base those layouts on the same instance of the Contact table, providing a single context for the relationships needed to support those layouts.
P.S. No apologies needed for the "newbie question" (your words). Your relationships (while probably due for some tweaking along the way) are already showing a bit more sophisticated understanding of relational design than some of us had when we started out.
Thanks! Will dig into this right away.
There are a couple of staff members that deal only with refugees, a few that deal only with volunteers, and so on. I though I'd make it easy for them and build a home/startup screen that had a button taking them to the layout with fields dealing only with the data they needed.
So, with only one instance of the contacts table, I would just need to link all the supporting tables (donations, staff, courses, families, etc.) to that one table?
Thanks for your quick and courteous reply. My background is in research design and analysis so I'm feeling a bit overwhelmed by database design. Want to keep it as simple as possible but make sure I'm building it the right way.
One thing I don't hear clearly expressed yet is the idea of join tables.
Yes; a single table for all people no matter what their place in the organization.
But you don't put all the fields in that table.
You use other tables, linked by ID, to handle various classes of information.
So for instance you might have a staff table; and a refugee table.
The refugee table would have contactID, countryFrom, date, etc.
The staff table would have contactID; staff position; start date; job title; etc.
You might have an Assigned table which has a staffID, date, and refugeeID.
Both staffID and refugeeID "point" to their respective records in the People table.
Following Mark's lead, I am going to point you to some examples that you can learn from or perhaps use as a starting point and then morph them into your own. It will be much quicker to get a system up and running as well as knowing that it was designed by pros and thus the system has a solid foundation from which to build on. This will prevent any instances of "geez, if I only knew then what I know now, I would not have dug myself in such a hole!"
Both Apps are from Richard Carlton Consulting and are free. Check them both out, look under the hood, you will be glad you did.
one is more specific to non-profits but the other may be stronger with some modules that can be repurposed.
Not trying to kill your joy of starting from scratch and learning as you go.
However, if nothing else, these apps should be a great source of learning.
You may find their relationship graphs a bit intimidating/overwhelming at first, but again, a great source to learn from and a great starting point should you decide to go that route.
Yeah, that's a great point. I had thought about doing that but was worried about one to one relationships. The joins would solve that.
Thanks for the advice Rob. The geek in me wants to build it myself but I may not have the time. I can at least start with breaking theirs apart to see what does what. I'll have to stare at the Starting Point relationship graph for a while to see if it sinks in. Definitely intimidating.