Database Structure

[CHawk]

I am trying to decide or resolve how to construct my filemaker database. I need to create what I would imagine is quite a complex structure. It includes:

Contacts - several different types requiring different layouts for data entry

Groups of contacts - presumably requiring relationships between the contacts and the different groups

Events - attended by the different contacts or groups. Various types of events - some requiring group data entry, some requiring single contact data entry.

Finances - payments between contacts and other contacts related to specific events

The whole thing seems to come to a web of relationships. How many tables are actually necessary? How many relationships are necessary to get the information required from the database? Can it just be a linear connection from one table to the next, or must each related table be connected to the others which I wish to take info from??

I hope that set of questions was clear. Someone please point me in the right direction...

[Fitch]

I'd recommend you sketch it out first on paper, keeping in mind that the relationship graph in FileMaker is not the same as an ERD. The "how many" questions will answer themselves. There isn't necessarily one right answer.

As for how they connect, you can pull data from any table occurrence that is related. E.g. if you have:

Event >-< Group >-< Contact >-< Finances

You would be able to look at an event and see all the related finances even though it is three tables away.

Dive in, read some books, don't expect to get it right on your first try.

[Fenton]

Event >-< Group >-< Contact >-< Finances

The trouble with the above is that it requires a single contact from a group. While it is true that you likely have a contact for a group, it would require some way to differentiate between him signing up as a person, or merely as a representative of his group. This could be done via checkbox I imagine.

An alternate method would be David Graham's separation model. The critical line of your specifications is, "Various types of events - some requiring group data entry, some requiring single contact data entry."

This is a little ambiguous. I see 2 possibilities:

1. Some "types" of events are ALWAYS Group events only. All others are Contact events only.

2. Each type of event could have both Group and Contact participants.

It's difficult to conceive of #2, because it's always people who take part in an event. But I suppose in the case of "booths", you would only know that said group wanted the both; you wouldn't know or care who staffed it. Conversely, a person could sign up for a booth.

If both groups and single people can sign up for an event (whatever it is :-), then the Graham separation method would help. I've written extensively about it in the last few days, so don't really want to go into the details. But basically it uses another "central" table, with 1-to-1 relationships to Groups and People. Record creation and ID assignment is automatic, via a clever exploitation of [x] Allow creation of related records.

What it would allow you do is to have a single foreign key from this central table as the single field to assign either a group or person to an Event. Assigning a group would include all its people.

[CHawk]

Firstly, thank you Fitch and Fenton for taking the time and effort to respond to my post. Your work is helping me greatly to understand the process required to build the database structure. Also, your example layouts forwarded to other people have really shed light on the type of layouts that might achieve the results that I need. However, I am only a little closer to my 'heavenly' truth that I will need to build the database. -:

Fitch - I have begun the incredibly heavy process of writing the database out on paper, and it starts pointing out the questions that need to be answered to make the database work - thanks!!

Fenton - I have spent the last few hours doing research from your previous posts, and information about this "Graham" separation method and trying to fathom the techniques behind the system. I have looked at the truck/trailer example that you gave in another post. From what I have seen, this may be the right way to go, but I think that I need to give you more info about the 'Events' and 'Groups' issue for you to direct me further...

Contacts - can be {Ordinary, Students, Student Couples(2 Students), Student Groups(Multiple Students), Teachers, Invited Lecturers, Event Organisers, Web Site Contacts, Shops/Businesses, Fan Club Members}

Events - can be {Individual Training, Couple Training, Group Training, Couple Group Training, Shows, Meetings/Appointments, ....}

Finances - I need to track financial records of individuals, couples, and groups attending different events. I need to track this for both the individual and for the couple or group to create a history of the contact, and a history of the contact within that particular couple or group - this is because the contact will not always be in the same couple, and I want to have access to the records of the couple/group as well as the individual over time.

Attendance - I need to track attendance records of individuals, couples, and groups attending different events. I need the same flexibility with attendance as I did with finances...

I hope that this extra info will help to define in your minds the way that I should proceed. I will do my best to follow the words of wisdom that ensue!!!

[CHawk]

I forgot to add that the contacts would also have to be able to be multiple 'contact types'. In other words, a contact could be an organiser, and also could be a student, or a lecturer, for example. I need to be able to track the history of contacts related to their overall activity, and in specific activities.

For example, if John Smith is a student, and also an event organiser - I need to be able to call up John Smith as a contact, have access to a portal which shows his student history, which events he has attended, his financial status - I also need to be able to see his 'organisation' history - What events has organised - how much he was paid, etc. - and finally, I need an overall financial status for all of his activities.

This is complicated further by the events taking place in different countries, and therefore being subject to different currencies. You can, I hope, understand why I am so hesitant to create the database without being more certain of it's eventual structure!!!

I hope that this makes sense. Kindest regards to you all!! Please Help!!!

[Fenton]

I forgot to add that the contacts would also have to be able to be multiple 'contact types'.

This would be handled by the join table which records their "participation" in activities, which has in a field on a record what "role" they had in that instance of that particular activity. What the contact "is" would be the ValueListItems() of a Value List of that Role field, filtered by a relationship from the person to that join table. In fact it is not really useful to think of what a person "is", it is always viewed in or thru the join table(s).

Here is an example file using a modified version of David Graham's (Bit Tailor, LLC) "separation of entities" method (my name for it :-). The "modified" part is mostly that I used a more traditional structure for the "organization-people" join tables. I connected them to the regular "outer" table's table occurrences, rather than to the common "Contacts" table occurrence. I can't really see that it makes much difference. If anyone can, please chime in.

I also used the ID fields from the outer tables in the relationship to the common table, rather than just a single "Type" field. I saw that Stephen Dolenski did this on his example. I can't see that it makes any difference either. And in fact I'm not using it for anything really. The only use I can see is that it could be used for more flexible Sorts for reports in the Contacts table (though it means sorting more fields; like 2).

The main things to notice are:

1. Almost all the fields are in the Contacts table. They're on the 'outer' table as related fields.

2. The relationship to Contacts has [x] Allow creation of records on the Contacts side.

3. There is no Contact ID in the outer table at that time. It is created in Contacts, then passed BACKWARDS to the outer table (this is the germ of the method).

Another thing to notice is that almost all of the fields in the outer tables are related fields. Which means that Finds and Sorts will be on unstored fields. But it is a very direct connection, so is likely fine, unless you've got many thousands of entries. In any case it's the price you pay for being about to handle organizations or people as either separate entities or one, as needed.

[ I came up with a method for indexed Finds on the names in the outer tables, by using a pop-up menu on the ID, showing "only values from 2nd field", and toggling Browse or Find on for the fields. But it's likely not worth the effort, as it's limited for Finds, and pop-ups become annoying as the records increase.]

Org_People_fej.fp7.zip

[CHawk]

WOW - Thank you so much, Fenton. I have only had chance to have a preliminary glance at this so far, but I am going away for the next few days, and I will try to study it, and to figure out how to add the financial element.

I would imagine that I may have to create a table with accounts, or currencies, or even both, and then expanding on the currently employed technique in your example database, I should be able to get some results.

I will come back to you in about a week.

Kindest regards....

[Fenton]

I think that part of it is not too difficult (famous last words :-). Basically a Contact has a currency. The advantage of the whole "separation of entities" method (maybe it should be called "consolidation of entities"?) is that you only have to deal with the Contacts table for such things.

One area that's a little fuzzy in the structure is that there is currently no difference between an Organization and a "group," which would be something like a "couple". They don't really don't have quite the same attributes as an Organization, who you'd likely want to view separately in a list.

There is no reason why the structure could not be amended to add a 3rd entity table, Groups. It would be a few more table occurrences (TOs) on the graph.

In other words, the method is not limited to only 2 entities, it can accommodate multiple entities. It mostly just requires more TOs on the graph.

Couples, and other informal groups are always a problem for databases. They are kind of like People, but need to be seen as a single entity, recognizable by a single "name", for things like booking (at least for events that are couple-oriented); yet likely contacted separately when mailing; then there's invoicing? payments? Send to "primary" person? Send to both?

By creating a 3rd entity, Groups, you would be able to treat them differently from Organizations, yet still recognize them as one thing when necessary.

[Groups will really pay off when you get into addresses and phones. A Group will use its people's addresses and phones, whereas an Organization will not; a major difference in behavior, but I'm not sure exactly how it will all play out, as I haven't got that far. The decision you'd need to make is whether the people in a group are mailed and invoiced separately; likely yes to the first (if different addresses), and no to the latter (invoice only the "primary" person, the one who booked).]

Edited April 26, 2008 by Guest