one DB multi relationship (Thread 2)

[Peter Fenner]

Bonnie, it would help me out tremendously if you could tell me what you dislike about my solution.

Thanks - Pete

[broberts7usa]

I don't understand why he would be a company - ever. Why wouldn't his companies that he creates every 2 seconds be the companies. Why would HE be one?

[Peter Fenner]

If Chad has companies then they would be the companies and he would be the affiliated individual. If you never need to specify individuals as parties in properties, but only companies then the whole scenario is entirely different.

You could easily implement a system where you specify (in properties) a company and then based on that selection set other fields with relevant individuals from such company. IE: specify the architrectural company and then specify the main individual on the job etc.

Pete

[broberts7usa]

I've deleted this post. I was getting testy.

[Peter Fenner]

Sorry, since you said "Peter's eg2 doesn't work for me" I thought that inferred you had looked at the solution.

Pete

[broberts7usa]

If Chad has companies then they would be the companies and he would be the affiliated individual. If you never need to specify individuals as parties in properties, but only companies then the whole scenario is entirely different.

You could easily implement a system where you specify (in properties) a company and then based on that selection set other fields with relevant individuals from such company. IE: specify the architrectural company and then specify the main individual on the job etc.

Pete, this has been the case since day 1 of this thread.

[Peter Fenner]

Quote from previous thread: "Let me give you an example. This is by no means a one-of-a kind example, because it happens all the time: One of the principals in my co. is named Chad. Chad is a person. He has one set of email/phone #s/ address info etc. He also develops land. He also is a broker for my company, and brokers deals. He also owns many properties. Some of them he owns by himself. Some of them he owns in partnership with others (and he populates the world of NH real estate with new partnerships rapidly). He, and they, are all legal entities. "

From the above I assumed "Chad is a person". I never realized, initially, that Chad actually owned in companies. I always thought he, and others, needed to be individuals. However, he does own companies and hence these companies will be input. This makes the basis of my solution (being able to use individuals or companies in properties fields) unnecessary.

Pete

[Peter Fenner]

Defenitely the final word for the day:

To take as an example: as it happens I develop properties. I do this in my own capacity. This is not a registered business. If I wanted to lease space via The Kane Company where would I be put in the parties database? I would have to be a company. Is that correct?

Pete

[broberts7usa]

No, your name would be a person, and your company, if you had one, would be a company.

OK, I am the same. I am Bonnie. I own two properties. I should have one record for my name. Another for the LLP which owns my property. The properties would be two records. Let's say I am a broker too. The company I work for would be a company record. Let's say I also own (as part of another company) 2 other buildings. Let's get even more complicated and say I am Chad, who owns 20 gazillion buildings, and is part of 20 gazillion companies. Isn't that where we were getting? Isn't that where this thread started?

Sorry, I'm getting testy. This has gone on SO LONG. And still no answer, except for Ugo's: there must be a join file. That helps!

[Peter Fenner]

Sorry, I really was actually trying to just help. I never meant for it to go on so long. To make matters simpler I will back out of this "debate" so you can get "real" help.

[broberts7usa]

Peter, I hope you are sleeping.

There is no need for you to beat yourself up.

This problem will not make a big difference.

[Ugo DI LUCA]

To take as an example: as it happens I develop properties. I do this in my own capacity. This is not a registered business. If I wanted to lease space via The Kane Company where would I be put in the parties database? I would have to be a company. Is that correct?

You got it Pete. And this debate about Persons and Companies can be solved by naming the file "Operator.fp5". You're an Operator as a company could be. The only distinction is that you don"t belong to a group.

Bonnie, you're too much in and not enough out. Take some distance and you'll understand that you're :

Record_ID Operator#0003 as Bonnie the Broker if you have an "interrest" in any property,

and also

Record_ID Contact#001 as Bonnie, one of the Brokers from Brokers.Inc (Operator#0002)

[Ugo DI LUCA]

Hey Pete,

I should have started to make a sample too. I would have understood what was the selfjoin you were talking about, and what was the deal with it.

You statued that a Property had only one architect and one broker affiliated. In that case, for sure, we could link the Parties to the Properties using the Broker_ID and the Architect_ID.

But we should create a relationship to a file where each party is listed once only, thus the CompCont.fp5 isn't appropriate. The link should have been made to the Company file (or eventually but almost sure it won't be useful to the Contact file).

Now, this is Bonnie's case and she should tell us if really several Architects or Brokers may be affiliated to the same property. The sample you provided only listed the Architects and Brokers, but we may also need to list the Owner, the Seller (or visitors), and some other "fields". If only one of these record could be involved in a Many to Many relationship, the whole setting would have to be changed.

I agree that a Building would hardly have more than one architect linked to it, but in fact it happens. Working in the Construction industry (I'm a Tile Wholesaler), I often come with situation where a "Building Program" is splitted into several Architects Agencies, and it also happens that the Program would be proposed by several brokers.

You see what I mean ?

If we were to keep the structure you started with, we only would have to add a link from Properties to Contacts, in order to display the "individuals from a company" involved in the Property.

This could obviously be done by creating a temporary key (from a script in portal row - SetField (temp_ID, Company_Id) and use another portal to the CompCont to list all contacts related to the Company_ID in this Join File.

If Bonnie confirms that there is a Many to Many relationship, the structure would surely move to a twin-line item system with cross relationship (clever term for little job )....

Waiting for some clues on this....

The sampler is on hold.

[broberts7usa]

Now, this is Bonnie's case and she should tell us if really several Architects or Brokers may be affiliated to the same property. The sample you provided only listed the Architects and Brokers, but we may also need to list the Owner, the Seller (or visitors), and some other "fields". If only one of these record could be involved in a Many to Many relationship, the whole setting would have to be changed....

If Bonnie confirms that there is a Many to Many relationship, the structure would surely move to a twin-line item system with cross relationship (clever term for little job )....

Bonnie is confirming. I have stated many, many, many, many times in these two threads what the issues and my dilemmas are. There can be more than one owner, there can be more than one architect, there can be more than one broker, there can be a company managing and a person developing or vice versa, etc. So now that (I think) I have learned that there needs to be a "join" file, I guess I will wander off and try to figure out exactly what that means.

Thanks you two - Bonnie

[Ugo DI LUCA]

Thanks for the clarification Bonnie.

I think we've just encountered the dilemna I was talking about in the first section of this thread, and that I also partly explained in the Article Section:

There are very little chance in a business environment that any odd events could be anticipated while created the db. Therefore, setting a db structure to a Many to Many structure even if there is no reason (at first glance) for it should be a basic rule in my opinion.

Let's imagine what a mess ones would be into if when the files are working for a while, he discovers these odd situations....

Just in case, in the meanwhile Peter was browsing this, I'd like him to try out on his file as I found that both the interface and the tips provided were great.

Now, in short, here is how the affiliation could be established....

1. Instead of having a defined field for broker, architect and owner in the Property File, we would have a portal with all related Operators for that Property.

2. The function/category of this operator, instead of being defined by checkboxes when creating the record , would be calculated based upon the records in the Join File.

----> If OperatorN

[broberts7usa]

Hi Ugo - and Peter - and anyone else -

It seems we are still where the 2nd thread started, having determined already that this is a many to many relationship. I did understand your point 2 or 3 posts ago that one person could be many things, in fact I made that point at the end of the 1st thread ... and again above. I also agree with you that Peter's example files have a great interface and the tips are great too. (as usual he comes up with great stuff! ) But the structure of those examples is not many-to-many - and it does allow for a LOT of duplication.

Ugo your points above:

1. The properties DB has always had a layout (even before I started the first thread, in fact for over a month now) which contains portals - or rather, future portals to use when the relationship structure was determined - for all the different relationships. But then I wondered about how many DBs were needed and started the 1st thread.

2. Once it was determined (end of 1st thread?) that there needed to be a join file, I understand that there would be calculations to determine how the catgories are listed (although I have NO CLUE where to start on the calculations!)

3. There already are separate portals/future portals, currently, in the properties file, for "operators" who have 2 separate functions. So it makes sense that they would be listed twice, either twice in one portal or once in each of the 2 or 3 or 4 portals for all these categories. They are currently empty and all on one layout but that may have to change.

4. And this follows from my understanding of the above.

To confirm: I'm now splitting/redesigning my Parties DB (which is currently empty awaiting all these decisions) into 3, right? One for People (which you call contacts), one for Companies, and one which is a join which you call Operators (but I would probably continue to call Parties). After the relationships are created, then calculations would need to be developed to establish the (sometimes) many categories each of these individuals/companies fit into - in the join file. Do I have it right so far? After that, the portals can be created in the Properties file.

I think this is where we left things at 3AM my time Thursday morning (which is why you did not hear from me yesterday - it literally gave me a headache!)

Bonnie

[Ugo DI LUCA]

Bonnie,

It seems we all felt necessary to make a pause on this thread.

Now, I'm sorry to announce that you still didn't catched the structure.

The Operator File would be your previous Company File. We would have in here either the groups (companies) and the "Individuals" .

Not all individuals would have a record here. Only those who acts as important operators.

A secretary (or any other employee you feel to have a record for) would be listed and created in the CONTACT File, and would be affiliated to a record in the Operator file.

But each individual Operators would be listed twice :

- One in the Operator file

- One in the Contact file

Why ?

Because in the Contact file, a portal would list all the Companies in which the selected Contact may be affiliated, apart from his own record.

As we are in a Many to Many situation, as I explained, a Individual Operator may be also affiliated to a Company.

The Join File would be created to Join the Contacts and the Operators.

Another Join File would be created to Join the Operators to the Properties.

[broberts7usa]

Ugo, that helps a lot, but I am afraid I am going to appear very stupid , I don't understand still. I wish I could draw an ER diagram in this little box, but I don't know how. I will give it a try?

Contacts (people)

join

Companies AND Operators (or the join file below to Properties? this is what I don't understand

both join Operators (I call this Parties)

which joins

Properties

What am I missing?

sorry to be so difficult

Bonnie

[Ugo DI LUCA]

Hi Bonnie,

I wish I could draw an ER diagram in this little box

See attachment if this may help. Don't take attention to the ID's in the portals. This is a pdf file.

ERKane.pdf.zip