Many to many plus another?

[David Nelson]

Renting & managing condos for people.  An person can be an Owner  or a Renter or both even at same time.  And they can rent another Owners rental.  One Owner can own many Rentals.  And one Rental can have many Owners (partners in purchase).  Multiple Owners also have co-rented a third owner's condo.

 

I have imported all the address books into People table and excel spreadsheets and currently have following additional tables:

 

Condos holds details on the condo and mainContact from People (required for Responsibility Clause).

Owners holds ownership rights, financial aspects associated with other partners owning same Condo, based upon their ownership percentages.

Rentals is not created yet and where I am stuck

 

So I have

 

People --< Condos--< Rentals

People --< Rentals >-- Condos--< Owners

 

Does this look right?  Every time I try to figure how it should be in designed in the graph, my eyes cross.  I see many to many everywhere I look and both directions.  

[David Nelson]

I should mention that the Rentals will have dates rented (span), rate, tax etc and I thought I should insert ALL people associated with the rental into maybe two fields like an Owners multi key and a .  I get lost here. 

[comment]

One Owner can own many Rentals.

 

What exactly is a "Rental"? Going by your description in the second post ("the Rentals will have dates rented (span), rate, tax etc") it would seem that a Rental is a contract related to a single(?) condo - not to any owner directly.

[David Nelson]

Wow. Ok.  Confusion clears a bit.  I mean that each co-owner of the rental proceeds must split the money so they 'own' many rental proceeds from their Condos.  I guess that makes more sense.  You cut to it, Comment.  So maybe all I need is checkbox in people whether they are owner, renter or both?  That would be simpler.  Because their ContactID  PeopleID will be in their Owner table and also in the Rental agreement (in the Rental table).

 

Like you said, the rental is a contract.  Still not completely clear but feeling great hope and I can put a file together.  Would that help you?  It would probably help me except I've been trying to figure things on paper before I put them in file (learned from Ray Cologon at DevCon - called swiss cheese).  Oh.  Well all owners must sign the rental agreements approving the renter so there is that part also.

[comment]

I can put a file together.  Would that help you?

 

No, I think you're still at the ERD stage. So far, I see something like this:

 

 

See if I missed anything important.

 

 

 

all owners must sign the rental agreements approving the renter so there is that part also.

 

That shouldn't be a problem because - as you can see - all the owners are related to the contract through the Units table. Note also that the Ownerships table enables you - if you wish - to keep a history of past ownerships.

 

 

 

each co-owner of the rental proceeds must split the money so they 'own' many rental proceeds from their Condos

 

Would that be your responsibility, too? Tracking the rent payments and splitting the proceeds among the owners?  I hope not, because that could get pretty complicated...

[David Nelson]

As I walk through your diagram, it seems to fit but this is a new business so I have no prior patterns nor business rules.  Tracking the rent payments, taxes (estimates) and providing the figures to their accounting firms so they can swap funds would be what the business (using this program) is supposed to do.  We won't be touching money - only numbers.  oh that sounded funny... is that what this world has come to?  

 

An Owner may rent from another Owner so owe rental but may also receive proceeds from that same rental for their payment if he is share owner.   This doesn't sound like the Renting Contracts table though; more like Receipts or Transactions so if we are missing anything, I would think it would be this.  The rental agreements hold rules, signatures, and I didn't even consider where deposits should go.  I am probably way behind you yet.  We hadn't even thought of keeping track of the past ownerships.  That would save a lot of work for them.

 

I will review when fresh and provide more cohesive response tomorrow.  I have been up for 34 hours now.  No sense you wasting any more of your time on it until/unless I can confirm with a clear head.  I will also take a look at some excel files they've used to track their properties, now in new light with your diagram, and confirm we're not missing something critical.

 

Thank you Comment.

Edited October 10, 2013 by David Nelson

[David Nelson]

May I ask which program you used to create your diagram?  I am in the market and I like the looks of that.  It is easy to understand.

[David Nelson]

Okay, I believe we need transactions off the Renting Contracts as many transactions for a single rental Contract.  But maybe transactions are only between People, selecting the Rental Contract the financial transaction is against.  Okay, I'm guessing and I should instead just listen.

 

By the 3 blue People squares, do you mean use three different ids as OwnerID, RenterID and MainContactID in the People table?  I am trying to picture the key connections.  If three IDs, that would replace my dumb thought of multi field too.

 

Spreadsheets reveal nothing you haven't covered but there are many notes about documents.  I think I can figure that part since they mostly are scanned Rental Contracts.  Some mention condo pictures of damages where they will charge a Renter and pictures of improvements along with improvement costs.  Not sure if that is stepping outside the current need but I thought I should mention it.

 

[comment]

By the 3 blue People squares, do you mean use three different ids as OwnerID, RenterID and MainContactID in the People table?

No, you would use the same PersonID for all three. In a true ERD, there would be only one People box - but since I am somewhat FMP-biased, I prefer to indicate that the People table needs to play three different roles there (which in the RG will translate to three TOs). The same thing applies to the Units table, looking at it from the opposite direction - so I could have just as well drawn it like this:

In a practical RG implementation, you will need to do a bit of both - if not a fully-fledged anchor/buoy model (which I am not really fond of).

 

Okay, I believe we need transactions off the Renting Contracts as many transactions for a single rental Contract.

I am really hesitant to express an opinion based on so little knowledge on my part. My gut feeling - and really, it's no more than that at this point - is that each Unit is an account and that transactions are performed by a person against an account (or vice versa). So for example, a unit charges a renter for rent due (negative amount) while a renter deposits a sum into the unit's account when paying rent (positive amount). But again, this is something you need to work out in more detail with the people running the operation (or even better, with their accountant).

[comment]

May I ask which program you used to create your diagram?  I am in the market and I like the looks of that.  It is easy to understand.

 

 

http://www.omnigroup.com/omnigraffle

[David Nelson]

Hi Comment,

 

Very interesting how you reversed those graphs and it has taken a bit to work through it. I ended up starting a file because I got confused with which Ids would go in which tables and I thought dummy data would help. My plan is to re-create the graph in a new perfect file when ready.

 

I thought I had several problems. I would start a response post to you and then solve it as I wrote my post, trying to explain it. I did that three times!! I have decided to always type out my problems. I wonder if that will work on my teenage daughter? Rof.

 

The burden of the graph is gone and I can move forward. I have attached my file so you can see my new graph. If I am using wrong keys anywhere, I will be surprised now since I think I really understand.  

 

I am a bit confused on how to name the personID in the different tables. I added comment saying it was owner ID or renter ID.  I could have also named them OwnerID and then put 'personID' in the comment.  Same confusion with layout and table occurrence names.  I changed the name of the layout to just People since 'PersonAsOwner' seemed strange.  I would love to hear how you would handle these parts.

 

Thank you again for all your clear thinking and I will buy Omni Graffle on pay day.

 

ops forgot file.  here it is.

structure.fp7.zip

[David Nelson]

BTW, the problems I had were around the ids and which were one and which were many.  I know you showed it in your graphs and once I understood, it was clear.  Oh that made sense...

 

I had put renterID in RentalAgreements, you see?  One example of my dumb-ness that I had to work through.

[comment]

I believe it needs to be more like this:

 

structure2.fp7.zip

 

Without the bottom TOG, you cannot have an overview, from the point-of-view of People, of all that a person owns, rents, and cares for.

 

 

I am a bit confused on how to name the personID in the different tables.

 

It's a matter of personal preference. I myself have no problem connecting:

People::PersonID = Ownerships::OwnerID

etc.

[David Nelson]

Sorry, I was at my brother's house and I just got back.

 

This actually makes perfect sense.  That gives me great hope in this business and also points to you being an expert in explaining things and figuring out what I needed.  I am hoping to receive funds for hiring out and I hope you would consider working with me if so at least for the difficult stuff.  So far on this forum, between you and LaRetta and Barbara and all the others who have helped, I have finally convinced my employer and this other company both that they need a solution written.

 

Now I just need to figure out how. 

 

I have my base.  And now on to the fun part of the scripting and GUI.  I am going to try to create the conditioned value lists where if selecting Unit, only those people pops  and vice versa and, because I am in Transactions, I see both of them perfectly from there, I think.  I want to see if I can do it without looking up how because I think I understand it.  I am getting quite cocky just because I understood what you presented when in fact your presentation is why I got it and certainly not me being bright.

 

However, it gave me a nice ego boost and I could use it right now.  Onward and upward.  Thank you again for all your help.  I think I'm hooked.

I will name my keys like you name your keys.  If you are okay with mixing them then so am I.

[LaRetta]

 

I think I'm hooked.

 

 

Indeed, David.  And there is no known cure.  All one can hope is to maintain sufficient supply of FM stuff to appease the ever-increasing cravings.  

 

And this is the best place to get your fix.  

[David Nelson]

Hi LaRetta, thank you for helping my boss the other day.  He said you were quite professional and had it solved in under 10 minutes.  If FM is my drug then Comment is the top pusher.

 

I created the value list and it didn't work.  Both show no values defined but I did.  I downloaded 8 examples of conditional value lists even three from you and none looks like this one and I have lost my perspective.  I considered scripting it but I think that would be junk.

 

Should I ask here or open a new thread over in value lists?  I don't want Lee mad at me.  Kidding, Lee, you help us all the time.  

I was not clear.  I meant I downloaded three files from Comment.

 

Comment, may I call you Michael?

[LaRetta]

If FM is my drug then Comment is the top pusher.

 

Oh my goodness that is funny!  I'll bet he's been called a lot of things but not a pusher!  But yes, he pushes concise, clear logic.  And lately, especially today, he pushes quite a bit of humour as well!!