Trouble normalizing complex people relationships in architecture.

[Phred]

Access 2007 front end SQL 2008 back end. Currently in design phase. I am not looking for a specific answer but a point in a direction or a high level suggestion (the view from 20,000 feet). I am quite experience with design and architecture and normalization. My specific area of focus is people. My customer is a large law firm in a metro area that specializes in property tax reduction. Here is a small representation of the types of people that need to be tracked.

• Owner
• Owner of Record (could be different from above or be a corporation or an LLC)
• Tennant (changes regularly)
• Attorney (Owner, Joint Owner, Represents Owner, Owned by law firm)
• Referring Attorney
• Law firm
• Spouse Owner (Could be married, friend, Significant Other not recognized by marriage, Girlfriend, all possible combination but they are associated with the property)
• LLC Owner and members
• Appraisor
• Interpretor Owner only speaks Korean
• Legal Guardian
• Judge
• Clerk
• Attorney, Owner, LLC, Owner with a corporation of lawyers, with one representing the owner father
• Corporate owner, In house counsel, on and on.
• Friends of any of the above because they referred the Owner

The potential combinations are truly staggering. Business rules include 1. Any Person can belong to any group or multiple groups and fill multiple roles. 2. A person is never deleted, ever, even if deceased, as you have wills, dependents, and estates. An attorney could be disbarred and still remain in the system. 3. It is possible a person may not be related to any property and still exist in the database.

It is a matrix of people and relationships and it must be manageable in some sane way. So this becomes my design standard. I must build a flexible construction of tables and relationships that takes into account all the variations and that does not stray to far from normalized structures. Honestly it sounds like a Cube to me, but I’m not going there.

So I am looking for high-level advice. Developers must have faced the same type of problem with products, etc. Point me in a direction. I know I am going to have a lot of Join tables. But first I have to construct it.

Thanks for reading this far.

Phred

[June7]

This is like a family tree. Relationships and associations just about the hardest db type I can imagine (manufacturing comes close).

Search forum: family tree

Here is one https://www.accessforums.net/forms/r...rds-26006.html

Search forum: associates relationships

https://www.accessforums.net/databas...ase-18426.html

[orange]

Further to June7's advice, you may get some useful concepts/ideas from http://www.tdan.com/view-articles/5271/

Good luck.

[Phred]

June7

One of your links had as an additional reference at the bottom a schema of a Genealogy Family Tree. This was very helpful because it shows the exact type of issue I am dealing with but in a simple ER diagram. I will use it as a base model for my situation. I don't know if there is a way on the Forum to post this problem and solution for others but I would be pleased to write it up and post it.

Thanks Phred

[Phred]

Orange:

Thanks for your reference, I have the article. It essentially is the written version of the diagram from June7, especially good coming from Len Silverston.

Thanks for your help.

Phred

[Phred]

One additional resource I discovered is this link to Youtube.

The session is Logical Data Modeling Complex (non-binary) Relationships. From Wilmington University. Very practical. A skillful use of multiple joins with simple illustrations.

http://www.youtube.com/watch?v=ZTPAMJ9MzdY

Phred

[orange]

Thanks for the link Phred. Her videos are relatively new (summer 2013). Nice job on generalization and aggregation.