Jump to content

Individuals and groups


GarryM

This topic is 6323 days old. Please don't post here. Open a new topic instead.

Recommended Posts

I’d really appreciate some help please. I am starting out on rewriting my 8 year old property business database (diary/contacts/properties/tenancies/etc.) and unlike last time when I just dived in, this time I’ve decided to do it as properly as I can. I’m trying to get my head around the relationships and tables I need for the contacts element to function as I want it to and how contacts will relate to the other parts of the database.

I need to relate certain contacts to properties, tenancies and to transactions (I aim to add double entry accountancy but after I’ve succeeded in the first bit). I will have an “individual” table and a “group” table. Groups might be companies, partnerships, tenants (often a collection of individuals). Some contacts are members of several groups (e.g. one person can be a company director, a trustee, a partner, a club member, a joint tenant) and of course a group will have several individuals belonging to it. I can see this is a many-to-many relationship which I can resolve with a join table which, until I think of a better name, I’ll call “groupmember”.

I can then relate tables for notes, numbers, addresses to individuals and groups. If an individual is a member of one or more groups I can display their information on a specific layout with portals displaying the group info’ in addition to portals showing their “individual” info’ (direct number, home address etc.). If they do not belong to a group I can have a different layout just showing the “individual” info’. I’m fairly confident I can sort the relationships and calculations to make this look OK.

BUT I’m really struggling to make sense of how I can carry this through to the other tables like properties, tenancies and financial accounts where again the relation could be to either an individual or a group. I can see it’s possible but it seems really cumbersome having to have calculations everywhere and different layouts with different relations depending on whether the related contact is an individual or a group. I can imagine the reporting will be a nightmare as nothing relates back to just one contacts table but two: groups or individuals.

Could someone either give me some encouragement to stick with this if they think it is sensible or alternatively, please tell me that I’m making this too complicated. My existing system just has a contacts file. When a contact is related to a tenancy or property I have used a repeating (foreign key) field in the related file so that several contacts can be linked. It’s not a technique I’ve seen anywhere and is probably not recommended. It works but I can see the approach of separating out duplicated information (company address for example) is better. Also, for accounting purposes the “customer” ought to be the company rather than an individual if that reflects reality.

If you’ve read all of this then you already deserve a beer on me! Comments will be much appreciated.

Link to comment
Share on other sites

  • 3 weeks later...

My earlier post didn’t generate a response so I’m trying again. Is there anything fundamentally wrong with a design that relates tables in this way:

Customers --- Groups --- GroupMembers --- Individuals

Customers --- Individuals

My point being that every customer is an individual or a group but not both. From a layout point of view this approach is a bit messy because the related contact information for a customer record is derived either directly from the individuals table or via the Groups and GroupMembers tables.

I’m a novice and not afraid of the extra work but I am very much aware of articles written by experts who, when asked to modify a database, say it has to be completely rewritten because the underlying design was poor. Is this poor design?

Link to comment
Share on other sites

Don't Group Members and Individuals effectivley mean the same thing?

As for the poor design... The above looks fine, but there are a lot of ways it can become poor. You'll probably just have to live and learn like the rest of us :B But on the upside like i said, the ... fundamentallity looks good.

Link to comment
Share on other sites

Thanks Genx. Group Members is just a join table between Individuals and Groups as individuals can belong to more than one group.

You're right about there being "lots of ways it [the design] can become poor"! The more I learn about FMPro and d'base design generally the more pitfalls I see that I was blissfully unaware of before. I hope the effort will be worthwhile!

Link to comment
Share on other sites

The more I learn about FMPro and d'base design generally the more pitfalls I see that I was blissfully unaware of before. I hope the effort will be worthwhile!

I think that's a general thing.. I know i feel that i've done something bad every other day... i.e. used 10 scripts where i could have used 3 or used 4 layouts for reports where i could have used one if my structure had been right... that sort of stuff -- but you can only go back and fix so much, but you save yourself making the mistakes next time so its all awesome :)

Link to comment
Share on other sites

This topic is 6323 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.