The "people" problem

[FestiveEmbalmer]

Here I am, undertaking my very first filemaker project- my nonprofit just "volunteered" me as designated inhouse dev. - and I am utterly stumped as to the best way to structure my data.

Allow me to ignore the enormous scope of the project momentarily and just focus on the people problem.

I found a more abstracted form of my problem in the RD theory forum, but it had no responses.

http://fmforums.com/forum/showpost.php?post/241269/

My company has many clients for whom we provide financial management. To satisfy our reporting duties to the government we must track a lot of other people such as landlords, co-tenants, mental health caseworkers, etc.

Initially it would seem these should all be separate tables, however they actually overlap. Many of our clients are co-tenants of each other, and some of them have acted as landlords. Co-tenants who are not clients often become clients themselves or will continue to move around being co-tenants for still other clients. I think we have even had 1 client who in the past was a caseworker! Another consideration is that many of our clients move away or are incarcerated only to return and begin receiving services again. (Return clients are identical to new clients as far as our system is concerned.)

So I am beginning to think that I should just have a People table for all of these things. Client accounts could be a separate table that pulls vital stats from its parent People record. Is this viable?

I imagine I would have to have a separate table for each subtype of people.

Or would it be more sound for me to have self-join relationships in people for dedicated fields such as landlord, caseworker? I don't think this is viable since I not only do not know how many co-tenants a person may have, I also need to be able to access all previous instances of those relationships.

I also see two more connected issues in the near future:

1) I will be needing to store each LIVING ARRANGEMENT - an intersection of a landlord, any number of tenants, a location, and a Rent amount. Changes in any one of these details must be tracked for reporting. I have no idea how many tables I will need to enact for this until I know how to address the "people problem."

2)Caseworkers, Landlords and other individuals we must track are often connected to a corporate entity (company, agency, etc.) and I will need to have that relationship represented appropriately...but what about cases where the an agency or company is directly related to a client. When a realtor company is a landlord, for example.

Dealing with subtypes is too basic an issue for it to be unique; I want to know how you do it, wise ones!

Thank you in advance, for assistence and direction.

[Anuviel]

Wow, sounds complicated. Good luck. I am a novice, much like you. As far as I know, have heard, each type of different data should have it's own table. If one person can appear as a landlord, caseworker and client I believe that at least 3 tables would be needed, joined with their respective unique ID's. What I found, that helped me, is to try and make a picture, draw up a database, like a pseudo code on the paper and work out everything that I will most likely need. If the data is separated, it is also easier on the reports, should provide more flexibility.

Disclaimer *** Get more opinions before taking my advice due to my FM skill level ???

[Fenton]

I would start with physical reality, and try and make it work. For example, you have people, and you have properties, and within each property 1 or more units.

A person is a "landlord" if they perform that "role" for a specific property. So it is not an intrinsic attribute of the person, but that they are the "landlord" of the property. Hence their "person ID" is in the "landlord ID" of the Property.

If multiple persons own and are landlords of the property, then the "landlord ID" is not in the Property table, but in a "join" table between Property and People.

Another join table between Property and People would be for Property_Tenants; there would often be multiple tenants. If any property had multiple units (likely), there would be a child of Property, Units, and a join table between Units and People for Unit_Tenants. The Tenants for a Property would be either in the Property_Tenants OR in the Unit_Tenants (but never in both; a property either has units or it doesn't).

[ I suppose you could handle the above by creating a single Unit record for every property with even only 1 tenant, but I don't see why splitting them would not work; the ultimate target table, from Property, in either case is People.]

As to financial service and accounts, I don't really know. But it's definitely to do with a person, and likely to do with a property (or properties), and each participation has a start date and end date. There are questions however, such as "if a person is a client, are all their properties involved, or only specific ones?", "if there are multiple owners of the property, are all of them clients?"

[Ender]

Man, you pick a tricky one for your first post. The FestiveEmbalmer is diggin' right in, eh?

Well, I think you'll have to spend some time thinking though all the relationships in detail to decide the specifics about the data model, but I'm thinking you're on the right track with a Person table to hold the basic info for a person that could be in those multiple roles.

Working that through, a Role would be a table about each role a Person has. They might be a "Landlord", a "Tenant", or whatever. Then to record the relationships between people, I think you'd use a separate Relationship table, probably related to Role, so that a "Landlord" is related to a "Tenant" in one instance, or a "Caseworker" might be related to a "Tenant" in another instance. I think the Property association would be handled separately, where someone is associated with a property via a specific Property Association. See attached for my first attempt at modeling this.

I haven't thought through the Company/Agency to Client association yet. I suppose it depends on how they are related.

[FestiveEmbalmer]

Thank you for all your thoughts.

Anuviel - Even novice encouragement is still constructive for me. Lets hope as my head grows from knowledge and egoboosts it will remain that way.

Fenton - You gave me much to think about. I was already beginning to think of join tables between Persons and Location/Property as being ROLES in the sense that they would be 1-1 relationships with people. I was beginning to have an exponentially growing list of necessary join tables. Bringing it back to physical reality helps immensely. If you have time, let me know if I properly translated that in the attached diagram.

I also had not considered making a separate table for Units on a property since we normally evaluate based on 'household', not property. I suppose it would come down to how redundant that information is. (Processing....)

Ender - Yes: Tricky is as job security does...

So you would relate the persons through roles and then relate the property to each of the roles separately? I am trying to visualize that but its difficult since I already had it in my head to relate them through Properties. I tried to dl your file but it won't let me!

If I understand you correctly that is what I had planned for the Caseworker - Client relationship and similar to my Client - Debtor/ServiceProvider relationship. Perhaps you could look at my diagram and tell me if you think it will work.

I appreciate the comments, folks.

Whenever I start getting my hands all messy with DB entrails and embalming fluid I become bewildered by the abstractions. I keep reading your posts to clear my head.

So now that I have a preliminary structural ERD, I am struggling with the ramifications.

Where is the appropriate place to evaluate this from? (Reporting shouldbe related to clients, but can all the appropriate information be viewed and changed from that perspective? Example: Changes to landlord would produce what? a new landlord record? a new living arrangment record? a field change in living arrangement record?) *BLITHERS*

Perhaps I could make the change in one place and script appropriate changes elsewhere?

hm...

[Fenton]

Unfortunately the attachments are not downloading right now, and maybe won't until after the database move tonight. So we'll have to content ourselves with a thousand words instead -| I would reemphasize that you first need a table structure to hold the physical entities. If you try right away to incorporate all your business rules, before having the tables for the physical entities, then you'll get confused, and maybe create structures that have to be torn down later. Some of that is inevitable, but less so if you get the basics straight first.

I'm using the term "physical" loosely. But a person is a person, a property is a property, and an address where someone lives is something. The reason I suggested Units is if you have properties where there are apartments or whatever. Different people are tenants in different apartments. If you don't have this then ignore it.

I suggest beginning with only the basic fields. You can build a functioning structure with very few fields. I think it is a mistake to load up your tables with everything you can think off, build all kinds of layouts, then try and build the relationship graph; especially when the structure is somewhat complex, as yours will be.

You should use an "anchor-buoy" method for the graph. That is, a main Table Occurrence Group (TOG) for each of the main entities, People and Properties, and possibly Contracts. Otherwise you're going to have kind of a mess. If you don't know what this is, then you need to learn; it's not that complicated. It makes a huge difference; a naming convention is also mandatory.

The next "entity" after the physical ones is likely "Contracts" (or whatever you call them). These are not as physical, but have a reality. They also have a "time", starting and ending. A lot of the confusion of your many-to-many is going to be solved by the dates involved. A given property may be part of several different contracts, with several different people or sets of people, but at different date spans.

A person's roles are determined by their connections to other entities. They may or may not need to be explicity entered. Many of their connections are going to be via join tables. If a join table is for a specific role, a specific kind of connection, then there may be no need to further specify the role there.

Example1: Tenants of a property (or unit). This is a join table. Everyone in it is a tenant, for that date period. There could be such a role as "signed the lease."

Example2: Persons owning properties; this would be a join table between People and Properties; call it PropertyOwners (I like to camel-case my join tables, others use a "J" between them, whichever).

A record would have a PeopleID, and PropertyID, a purchased date and a sale date; multiple people could own the property, a person could own multiple properties. If that property had tenants, then the owner would be participating as a landlord, on that record anyway. You could explicitly mark them as a "landlord" in the join table (as you'd likely know that, and it's not likely to change during their ownership), or you could just look if they had tenants. Probably the former; though it is redundant, it may speed up other operations considerably.

A person could be an owner of one property, and a tenant at another. Those are 2 different join tables, not a problem.

[Ender]

Here's that attachment again (hopefully it will stick this time).

[FestiveEmbalmer]

:) The file has continued to not work!

I'm going to try and upload my current graph, I want to know if it appears to match what you guys have been telling me. I also included companies as I had mentioned would be necessary.

I am basically creating sandboxes for each necessary system feature. Next I will be working on the bank account-check register portion of the application.

Am I following the anchor-buoy model appropriately? I absorbed as much as possible from http://www.kevinfrank.com/anchor-buoy.html, but thats the only substantial resource I could find.

I understand that I should have a TOG for each main table and that the layouts should be tied to the anchors. I understand the practicality of the model (I think), but isn't the downside that I have to duplicate relationships for all my layouts that are connected to a specific function but require multiple layouts?

Your guys' advice has been essential.

Thank you.

----------------

"An inconvenience is only an adventure wrongly considered; an adventure is an inconvenience rightly considered." - GK Chesterton (On Running After Ones Hat, All Things Considered, 1908)

LearningFile.pdf