one DB multi relationship (Thread 2)

Peter Fenner · May 22, 2003

EDIT: PLEASE NOTE THERE ARE PROBLEMS WITH ATTACHMENT - A NEW VERSION IS ATTACHED LATER IN THE FILE.

Continuing on from previous thread: "one DB with multiple relationships"

We have a main database (properties) which has various parties that need to be applied to it in various locations. These parties are either individuals or companies. Some fields will require an entry from either of the 2 party databases (namely Companies and Individuals). Attached is a possible solution that allows for ease of data entry in properties db. If you are interested in how to create a value list that sources data from 2 databases this file may be of interest.

To test the process of "bringing" parties into the properties database navigate to the Architect Info and/or Broker Info screens. The key functionality here is the method used to filter the list and the ability of this system to 'disappear' once an entry is made.

I am looking for brutal assessment of the attached files pointing out any unforseen problems as well as any simpler alternatives that offer the same functionality.

Attached file is in zip format.

Pete

Parties2.zip

Peter Fenner · May 22, 2003

You must add contacts (individuals) and companies with the script supplied. Using the "New Record" function would need to be disabled for use.

Pete

Ugo DI LUCA · May 22, 2003

Pete,

As you asked for constructive criticism, here are some features to be added to the files, fom the quick look I had at it.

I know though that due to size limits for samplers here, not all could be done...

1. check duplicates.

I was able to create as many dups in the contact file. You have now 3 Peter Fennerton

2. I have now (and still don't know why and how I did this) 2 Jacky King linked to the Kane Building, showing in the portal

3. this is very little adjustment. Change the archutect field to broker in the broker layout.

I'll work on it a bit more and tell you why and how Jacky felt she had to enter twice...

broberts7usa · May 22, 2003

Pete -

My first reaction when seeing it was "Great"! Now, I've played around with it. Sinking feeling.

Just by playing around, I've added lots of blank records. (Big taboo.) And duplicates (another). I tried adding the infamous "Chad" - our broker/architect/owner/developer/manager who is involved in or with many different companies. It's not happening. Nothing about him is displayed where I would expect it to be and more importantly there doesn't seem to be any way to relate these records to a property. Property-related fields I clicked on (portals) did not respond (or display).

I really cringe to critique, when you have done all this work to solve MY problem. You did say to be brutal? I am just reporting the first take - for my uses ...

I am not sure where to go from here. In my own head, from our previous thread, I am pretty sure that there need to be 2 DBs, one for people, one for companies. That's where I started on the last thread and perhaps we should start from there again?

Pete, thanks ...

Peter Fenner · May 22, 2003

Hi Ugo

Sure, there is a lot of enhancing still required. Your points are valid, yet I am particularly interested in the fundamental methodology used - whether there are key flaws and whether there are key improvements. But thanks for the initial feedback.

Pete

broberts7usa · May 22, 2003

Ugo, hi. Your Operators file ... well, maybe you can crunch it down for this discussion? Without too many calculations?

Ugo DI LUCA · May 22, 2003

Pete,

OK, what is strange about Jacky is that she only wants to appear in the Kane Building

In Companies -->Affiliated-->filter for K (Good job on this BTW)

1. you can enter as many dupes as you wish in portal

2. King always appear in the Kane Building, whatever company she is affiliated to.

Peter Fenner · May 22, 2003

Hi Bonnie, there are 2 databases for companies and individuals. Duplicates are easy to avoid by not allowing duplicates. As are blank records. Technicalities that I should have applied to the fields. The portals are all (except for affiliated individuals) read-only. You must add the architect from the Architect Info screen. Same applies to Broker. The portal displayed in Main Info of properties just displays the list of all affiliated parties as well as a link to their layout. This portal would not, necessarily, be available from your main info screen - I just put it there for the sake of this example.

Pete

broberts7usa · May 22, 2003

it's very funky. Chad shows up a zillion times when scrolling through records. But only once in a portal.

Peter Fenner · May 22, 2003

Jackie King was inadvertently duplicated by myself in the files provided. To test the solution properly please do the following: Delete all records in Companies, Contacts and Compcont. Now try it.

Thanks Pete

Ugo DI LUCA · May 22, 2003

Hi Bonnie,

Not really. I think Pete's file would be OK with some relationship adjustments.

As far as I could see, the links are implemented already, but the way he set this up, In my opinion, there would be a lot of scripts (to set the keys) rather than direct relationship.

But as you pointed out last day, as there are not really a "Operation File", there is no need for a big split of files. Company and Contact files are already there. It's just a question of how they relate eachother.

Oh BTW Pete, once a Company is categorized, we shouldn't be able to switch and delete the categories....

broberts7usa · May 22, 2003

But the portal DOESN'T display affiliated parties. It is blank. Therefore your brilliant disappearing link doesn't work.

Peter, you've worked hard on this!

I don't know where to go from here.

Peter Fenner · May 22, 2003

OK, Yes there are some problems. ******* I checked it properly - or so I thought. They are minor adjustments (although they offer major chaos). Sorry for the mess. I will quickly fix the various problems and repost the files. Speak to you soon - Pete.

broberts7usa · May 22, 2003

I should get my boss to pay you for this. Since I'll be plagiarizing heavily from whatever you come up with.

Ugo DI LUCA · May 22, 2003

Pete, Jackie wasn't a duplicate. I reload the files from the link provided here because when I deleted all records, there were some relationship settled that were fixed and related to no other records, thus not working.

Now, I've tried what I said, Affiliated, filter and select Jackie, and she appears twice in the Kane Building.

Then I affiliated Jacky to another company, and she also appeared in the Kane Building portal. Any company she is attached to seems to refer to the Kane Building.

There must be some kind of "bad relationship". As the Kane Building is the first record, all relationships end there. That's my guess, but as you did the files, it's hard to reverse and see what's inside this quickly.

broberts7usa · May 22, 2003

Me too. Ugo, you said it better than I could, but that's also what I found. A bad relationship? (I'm not going to go there ...)

Peter Fenner · May 22, 2003

The affiliation screen is where all the trouble is - it's duplicating records when it shoul not be. I am almost finished fixing this and will send correct version very soon.

Pete

broberts7usa · May 22, 2003

Another question. Your solution has 4 files. One which relates to my main properties file. Another one for contacts (people), another one for companies (ditto). And one called "compcont". Is that a join file? Or is that what my "Parties" file (with people/contacts and companies) started out to be?

Ugo DI LUCA · May 22, 2003

Pete,

The affiliation isn't the problem.

It's your portal relationship "Property to compcont" that is to be set.

You're using a "all key contactID" at left side, where the key should be Property_ID.

But then how would it relate to the Contact as there are no fields for a reference to a Property in the Line Item.

You can have as many dupes as you want in the line item, but there need to be an ID there to relate to the Property_ID...

Then your Multikeys relationship should be :

for the left side

Property_ID&Allcontact_ID& "PP" &

Property_ID

and at right side (in the Compcont)

Property_ID&Contact & "PP" & Property_ID & "PP" & Contact_ID.

broberts7usa · May 22, 2003

HUH?

You lost me on the multikey stuff. (Not that I've had time to check the relationships, but at least I understoood THAT.)

Peter Fenner · May 22, 2003

Hang Five

Ugo DI LUCA · May 22, 2003

Yes Bonnie,

CompCont = CompanyToContact

Now, from what I've just said, we need either a new line item (join file) for PropCont (PropertyToCont) or a big CompPropCont (which would relate the three files eachother), but I cannot find any logic here.

M guess is that the PropCont is missing ....

broberts7usa · May 22, 2003

Bear with me. This is my first DB (and you guys are obviously doing all my thinking for me - or along with me?)

A join file - in this complicated scenario - would function to populate fields where the "answers" might come from several different DBs? So finally we might get to the bottom of my friend Chad, who is a broker and a developer and (many other types) and also is affiliated with many other companies (because he keeps creating companies every time he makes a new deal) and also with different properties (everytime he snaps one up)?

Has Chad confused everything? I want to understand the concept of when "join" DBs are called for.

Ugo DI LUCA · May 22, 2003

OK,

Let's summarize the situation.

We are facing a Many Many to Many relationship here.

We have Many contacts to Many companies

We have Many Companies/Individuals to Many Properties

and we may even have Many Contacts to Many Properties.

So we need Many Line Items.

Using another example, I came up with same situation in another thread a week ago.

I provided a sample there for 2 lines Item and 2 Master files.

Have a look at this thread and download the sample (rough test file).

Here is the thread link

componentToProduct

Really similar situation

Many components to many products

Many products being sold

We want each componant to appear as some may change in the process.

broberts7usa · May 22, 2003

Sorry Ugo I just read your previous post.

CompCont = CompanyToContact

Sounds like a join file to me.

Which brings me to another question. How to set up the relationships.

broberts7usa · May 22, 2003

Yes or No about this ER structure please:

Properties (Main)

Parties (join): Companies and People/Contacts

Ugo DI LUCA · May 22, 2003

My ER Proposal

Properties

Companies/Individuals

Contacts

Line Item CompaniesTo Contact

Line Item Properties To Companies

That's all for Today....

Peter Fenner · May 22, 2003

OK, Sorry about previous attachment - I think I have it right this time. As usual, brutal criticism is encouraged! Important: Only create new records via the buttons provided.

Pete

Parties3.zip

broberts7usa · May 22, 2003

So now we're talking 4 additional DBs?

First it was Properties and Parties. That's where all this started. The we began talking about splitting parties into individuals (contacts) and companies. Then we started talking about a join DB (take pity on me, I am still trying to understand this). So now we are talking

(from Ugo, above)

Properties

Companies/Individuals

Contacts

Line Item CompaniesTo Contact

Line Item Properties To Companies

I am about to tear my hair out, I DON'T GET IT.

Peter Fenner · May 22, 2003

Ugo, how do you get the companies or individuals into properties via your system?

Pete

Ugo DI LUCA · May 22, 2003

Pete,

I'm afraid you won't have any better luck here.

Try to remove Chad Id as a broker field for the Kane Property.

Enter yours using the filter.

Then check the portal.

You are there with 4 records (all your records from CompCont I think)

broberts7usa · May 22, 2003

i am assuming the line items are meant to be relationships, but still ...

Peter Fenner · May 22, 2003

Hi Ugo

Mine works fine - I think. Chad is also specified as the architect. The value list on main file (in the portal) just shows the parties type (all types).

Pete

Ugo DI LUCA · May 22, 2003

Ugo, how do you get the companies or individuals into properties via your system?

Using what I call "cross relationships".

3 contacts are related to a company

So we have 3 lines in a Line Item with Company_ID

Comp003---Cont001

Comp003---Cont002

Comp003---Cont003

2 Companies are related to a Property

Prop001----Comp001

Prop001----Comp003

Basically, this means that 3 contacts from Company Comp003 are related to Prop001 through relationship CompToContact.

In the reality, you could select those Contacts from the Comp003 (from a value list) that may be attached to Prop001.

I'll try to attach a sample file tomorrow (test file though, not a clean interface as yours).

But if you've seen the link I provided about Many Many to Many relationship, you should see it works.

Are you with me ?

So we have 2 line items.

broberts7usa · May 22, 2003

I'm outta here for tonight. I'm overwhelmed and I still don't think my question about the DB structure has been answered, and I couldn't look at another file to save my life. This back-and-forth is why chatrooms are good. These kinds of questions would be useful if we were all looking at the same thing at the same time - but we're not.

Go for it you guys, I'll watch. And hope to solve my problem tomorrow ... Bonnie

Sign In

one DB multi relationship (Thread 2)

Recommended Posts

Create an account or sign in to comment

Create an account

Sign in

Important Information