I am starting to work with the Party Model and have developed a small sample file to test the structure.
As you can see from the screenshot I have built a table for people, organisations, parties, the roles a party can have and for the types of roles.
Additionally I have built a table for one of the party subtypes (Employees).
I am not sure how to represent a subtype of a subtype.
Assuming I have two subtypes of employees:
those who are employed by a company
those who work on a fee based contract.
There are employees who could be both subtypes at the same time (some hours they work as employed personnel and some on fee basis).
I guess the way to go is to create three PartyRoles records:
one as employee
one as salaried employee
one as fee based employed
If needed I could then spread those out in separate tables (as I have already done for employees).
Is there a better/more sensible way to do this? Any other suggestions?
Maybe it is strange to ask, if I already have an idea. I am still a bit irritated that in using this approach, the three records would not be related together as they would be in a non-Party-Model approach. (Where I would join the two subtype table directly to the Employee table).
Party as in Fiesta/Event? or as in Entity Taking Part of an Agreement?
Functions - Assignments - People
PartyRole - Assignments - Person
You have the function or what you call the role they have to fulfill at the event. You have a join table that defines the conditions under which a person is assigned to a role. You have a person table.
The assignment attribute "modality" specify how the person services are retained, either as an employee or on fee basis.
So you have all the information about the role in the table role(or function)
All the information about the assignment (date, time, modality, fee schedule or salary schedule, etc) in the table assignments
All the persons information such as name, contact information, skill levels, availibility days, etc. in the person's table
I am assuming you are talking about the party model as described in "The Data Model Resource Book" or other similar book.
The Role Type table is recursive. That is, a role type can reference another role type. That's how you would get a sub-type role, a sub-sub-type role and so on. The Role Type table contain two IDs for this: the ID of the Role Type, the table's key field; the ID of the parent Role Type, an optional field that allows linking to create role type hierarchies.
Recursive tables may not be possible in FileMaker but a parent-child relationship can do the trick. Add a ChildPartyRoleType table to your relationship graph and that should do the trick. The rest is field stuff. For example, you could add a parent Role Type ID List field to list the role type's lineage if you need to find all related role types for a given parent or child.
As for employees with multiple roles…
I would say your guess is correct and the most logical approach to solving your employee problem. Your Party Role table is a join table that allows a party, here an employee, the ability to have multiple roles. Note that if salaried employee and fee-based employee are both sub-type roles of the employee role, then you need not assign the employee role to the employee party. A sub-type assumes it is also it's parent type. That's like saying a bicycle is a vehicle, a car is a vehicle, but a vehicle is not necessarily a bicycle or a car.
For simplicity, I typically start a party model in the fashion shown below. It's easy to add tables that attach to the party record.
In your relationship graph, why don't you have more than one record in PartyRole for each employee type? Are there business rules that prevent you doing that?
@bdbd: Yes, your assumption is correct. I am talking about the Party Model as described in the book.
I understand what you say about parent roles and child roles and how to represent them. Thank you for this suggestion. I had not thought about that.
However, I do not understand the following:
„Note that if salaried employee and fee-based employee are both sub-type roles of the employee role, then you need not assign the employee role to the employee party. A sub-type assumes it is also it's parent type.“
Does this mean you would only store two records in PartyRole („salaried employee“ and „fee-based employee“)?
That way I would not have a general overview over all employees.
Let’s say I want to contact all employees (regardless of there subtype), I would have to script a search that looks for both subtypes. If I have an additional PartyRole record for „employee“ (in general) I could simply base my search on those records.
Or would you then make a search for the „parent role“ (as you suggested above) without having an explicit record in PartyRole for it?
I hope I could make myself clear enough.
@Malcolm: I guess I was not clear enough in my post. There is nothing preventing me from having more than one PartyRole record for the subtypes of employee.
In fact, it is my idea to have several records there (one for the „parent type“ and one for each subtype).
The point @bdbd made is exactly what confused me: Theoretically it would be possible to add a record for „salaried employee“ without having a record for „employee“. That would make it possible – using his analogy - that a car is not necessarily a vehicle.
You have already foreseen a sensible reason for storing the super-type ( employee ) instead of inferring it from the sub-type, that is that you want to be able to search for all employees regardless of what type of employee they are.
You could set up your system so that selecting "Status_IsEmployee" (or creating a related record in the employee table) would then allow you to specify the sub-type of employee for that record. And to make impossible to select an employee sub-type otherwise. That would ensure that a search on "status_isEmployee" would find all employees. An alternative is to have the related record generated in Employees whenever one of the employee sub-types is selected. In either case you have the same result, the party role record stores the information about the sub-type and the information about the type ( is an employee ).
The trick is that your suggested relationship between PartyRole and Employee isn't going to be sufficient to record more than one employee role. If you only want to flag that the person is an employee in that party role, then it works. But you can't use it to record the status. You cannot modify the status without breaking the relationship. And the status of each role needs to be distinct from the others.
It can mean that. Remember that I mentioned you could add a lineage field to find all the role types for a given parent or child? This is a possible embodiment of my suggestion:
FileMaker has a feature that I usually discourage but is particularly useful here: fields can hold multiple keys that individually work in relationships. Using this capability, the idRoleTypeLineageList field would contain a list of RoleType::id, starting with the current record's id, followed by the parent record's idRoleTypeLineageList. The membership equation (the one with the ∈ in the diagram) is simply an equal sign in a FileMaker relationship predicate.
What does this give us? Say you want to find all employees. From a Role context, find records where RoleType::idRoleTypeLineageList contains the employee RoleType id. The result will contain employees, salaried employees, temporary salaried employees, permanent salaried employees and fee-based employees (I added a few). Say you want to find only salaried employees? Same type of search for salaried employee, only this time the result will only contain salaried employees, temporary salaried employees, permanent salaried employees.
Let me emphasizing something important. I started by saying it CAN mean that. It doesn't have to mean that. You could well need to link all three employee role types to role records. That would depend on what else needs to be captured for roles. The Role table may be linked to other tables to capture additional information. The additional information could change based on the role type. An employee role record would be needed to capture employee information in such a case.
That would be my prefered method and I think I will implement it this way.
That is something I do not understand.
Can't I create only one record in Employee and then two records in PartyRole table that are flagged as "isEmployee":
ID_Party
RoleType
From
Thru
IsEmployee
18
Employee
01.01.22
31.12.22
1
18
Employee
01.05.23
1
Of course I would need to join everything related to one period of employment to the PartyRole record, but everything concerning the employee in general could be related to the record in Employee. Do I overlook something?
No, that was exactly how I meant it. Assuming I do not use your concept of "ParentRoleType" and only script the creation of PartyRole records for employees without them being linked by such a ParentTypeID. Then in theory it would be possible to create a record for "salaried employee" without creating the record for "employee". In this case, a salaried employee would not be an employee (= a car would not be a vehicle).
Regarding my sub-subtypes:
I guess I could then use some separate tables for the subtypes If I need them and tie them either to the Employee table (see screenshot) or tie them to the PartyRole table (as I did with Employee). Anything wrong with this?
Yes if you use the person ID as the key. but you have the status field matching a constant. Why? It’s not strictly necessary. You could use a script to set isEmployee in partyrole. Or use a calculation field.
As I use the PartyID in both the ID field of Employee (as one party will always have only one employee record) and in the PartyRole table, I would see all PartyRole records for the party (not just the records of the role "employee"). I have seen this technique in Karbon (by GeistInteractive).
My mistake. I used the wrong field name. But the question still applies. What is the purpose of adding the "status_isEmployee = ONE" criteria to the relationship? My feeling is that it's doing too much work and that makes the system fragile.
From the PartyRole side, if you change status_isEmployee to empty or zero the relationship to Employees is broken. In the PartyRole record everything looks OK, but in the employees table the data is still present. From the Employee table you have a record but no link to the data, just an orphaned ID. If you only ever plan to work from the PartyRole table it may not matter. If you do plan to work from the Employee table then it's a problem that you have IDs which do not match associated data. And of course, simple questions like "how many employees are there?" cannot be answered easily.
If I delete this criterion from the relationship, then I will see from the Employee record all other roles this Party has had. This screenshot shows the layout "Employee" with a portal to PartyRole:
If I use the flag field together with the constant I only see the roles regarding employment:
This flag field (Status_IsEmployee) is not meant to be modified. If the role record is still active is determined by the "from-thru".
So far I have only shown some aspects of my sample file, because I had a specific question. As I progress in my thinking and planning there are more and more questions.
As I am building a new solution expected to grow, get really big, with lots of roles and different aspects it will manage (internships, employees, speakers and their presentations, the attendees of those presentation,...), I thought the Party Model would be the professional way to tackle this. The alternative was to create tables for all the roles I have in the system and work without a „PartyRole“ and a „PartyRelation“ table, as I have done so far in my projects.
Now, as I am experimenting I find myself creating all those separate tables for the individual roles anyway (see the posts above). Am I overdoing things? Is using the Party Model the right approach? What are the benefits?
In „PartyRelation“ I can model internships as records between a party acting as intern and another party acting as internship place. So in fact those records represent an internship. How do I proceed to store more information about an internship (name of the internship, number of visits by the tutor, evaluation, grade,…)? My solution would be to define a new table „Internships“ (see orange table on screenshot). I think it sould of course be connected to the PartyRelation table as this table holds the basic information (start date, end date). Is that correct?
Assuming a person has mutiple internships: Of course there are several records in PartyRelation, but should there be several records in PartyRole (roleType: intern) for each internships of this person? Or should there be only one PartyRole record as the amount of internships is already seen in the PartyRelation records?
If it is better to start a new discussion instead of adding to this one, please let me know.
Adopting the level 3 party model means you should avoid creating tables for any individual role type. That defeats the model. Adopting the level 2 party model means you should avoid creating a role type table and only use individual role type tables. There is no more value in a role type table. One way to logically solve this question is to ask another: What are you uniquely capturing in individual type tables that can't be captured in the Party, Role or Role Type tables?
I am perhaps misunderstanding what "PartyRelation" means. Shouldn't it be the relationship between two parties and not between two roles? Isn't the constraint a relationship constraint? If so, perhaps you need a relationship type table to allow you to enforce constraints. An ERD should help you solve this problem space.
Perhaps I am misunderstanding. Internship is not a role. Intern is a role. If a person can be different types of intern at the same time, that points to the need for sub-intern role types. If there is only a generic intern role type and interns can be in multiple concurrent internships, then that has nothing to do with role or role types.
Specific data could be for example a student identification number. It makes not a lot of sense to store it in Party or Person as other people (employees,...) do not have one. But there might be another and better way of doing this. Maybe I am to much rooted in my habits.
There seem to be two reasons for the separate tables:
I capture data that is specific for this role a person can have. Corn Walker and Ernest Koe talk about those separate tables (DIGFM: Party Down at School with 34 Proof (7/12/2018; Santa Clara, CA) - YouTube). I think they said something along those lines: If a role is very important in a system it probably has its own table.
I can base layouts on those separate tables and do not need to constrain finds (like I need to if I base the layouts on PartyRole). That is something Dave Graham posted somewhere on the Claris community.
I guess I was not very clear on point 2 and 3:
Yes, it is the Relationship. But as Len Silverston says in the book: You can model it either way: as relationship between parties or as relationship between two roles. I find the second option more appealing as I can directly see which role the party plays in this relationship.
For example: If I connect the parties "John" and "James" I have still to specify their roles in this relationship. I chose to connect John's role "father" with James' role "son" and everything is clear.
Maybe I overlook something. What would be easier if I would connect the parties themselves in PartyRelation instead of using their roles?
Yes, internship is not a role. Maybe an example makes it clearer what I wanted to ask:
John is an intern. He has three internships:
1st internship: February 1 - February 10
2nd internship: May 1 - May 21
3rd internship: October 1 - October 31
Of course I do create records in PartyRelation for his internships (between him and an internship place), but do I create three PartyRole records?
PartyID
RoleTypeID
From
Thru
ID John
ID „intern“
February 1
February 10
ID John
ID „intern“
May 1
May 21
ID John
ID „intern“
October 1
October 31
Or do I create one PartyRole record (without dates) and join it to the three records in PartyRelation?
As far as I know the "from" and "thru" in PartyRoles helps to store if someone has a certain RoleType multiple times, but it seems a bit odd to create three records in this case.
I hope I was clearer this time, but I have doubts
Maybe something Beverly Voth wrote in a post helps:
" You have a historical record something like being in school, each "year/grade" is one role. Each has a start and end. A child in 6th grade is not "current" for the "role" of a 1st grader. But it does show the history.
Same with Individuals of an Organization. They can also have the same role that starts and ends several times."
That is the reason I thought about creating those three PartyRole records.
I see. Party A, department head role, is the boss of of party B, intern role, for a period of time. I have a feeling that the join table that captures this relationship will still require descriptors to define the nature of the relationship.
There are definitely many ways to achieve 3. When I look to the question of re-assigning an identical role to a party or not, the question I asked myself was if the roles were concurrent or consecutive. Thinking a person could only assume a specific role at a company once at a given point in time, concurrent roles didn't seem plausible. I can, however, see the possibility of assuming two different positions at the same point in time.
So yes, I can see the intern role being assigned to the same person for three different internships. This goes well with the idea of relationships between roles: internship 1 had one boss; internship 2 had a different boss; internship 3 had yet another boss.
I’ve been reading all posts here and must admit that I did no get what your solution shall achieve.
When starting from scratch it can be helpful writing a process before doing database design. Or have a description of the task in natural language.
Such information could facilitate the discussion and help.
From and Thru allow you to use the design pattern to record a historical record.
If you enforced a rule that said you may have only one record in PartyRole then the historical data would have to be moved into PartyRelation.
The reason for confusion is that you have choices. Often, there is only one suitable pattern because (a) you can't design in another way or (b) when you have choices you also have business rules that determine which option to use.
I suggest you opt for the pattern you think is simplest. Don't create cognitive effort for yourself.
