one DB with multiple relationships

[Ugo DI LUCA]

Pete,

You've done good work here in little time.Congratulations.

I like the navy (that was a song or was it "In the navy" ? ) and the message box looks pretty cool.

This is obviously a good starter for any contact db, and the basic structure you have chosen can apply to many other businesses.

Bonnie may now work on the "contacts" part of this ER as we know from this thread that she has to handle some odd situations that surely would involve a line item from Company to Contact.

At least, that is the way I think it should be to handle the Many to Many relationships.

I'd look to the file closer and let you know my "beginner" feeling about some shortcomings.

Thanks for the support provided anyway.

[Peter Fenner]

Hi Ugo

Why would you ever need a line item from company to contact? Any one contact can be allocated to any one company. This aspect is not included in my example, but would be straightforward to implement.

Pete

[Ugo DI LUCA]

Hi Pete,

That's some logic that verify to not be logic nowadays. I have at least 20 contacts in my own db that are attached to different companies....

When business evolves, you surely would end with these kinds of odd situation, some that you can control with a one to one relationship, some that you can't.

I started a thread on the subject some weeks ago in the Database Theory section. The title was "Fixed relational design vs Business Evolution". The more I work on my dbs or on some others, the more I feel like it's kind of more secure to plan a db on a one to many, or even many to many relationship, even if at a start, there should not be any sign of such needs. It surely is easier to move afterwards to a one to one, than the contrary.

I can't remember the name (was it Chuck ?), but Bonnie did specify that some contacts could be attached to different companies, or act as individuals. In that case, you surely would have a unique contact_Id, but 2 or more companies attached to this contact.

Just have a look at my old files here on the forum. Even if it was a start (and really ugly compared to yours), this feature is included. I've mentionned some steps for Bonnie in order to "move" efficiently in this test db (have a look some posts up in this thread).

[Peter Fenner]

My post is badly worded. Without the need for a line item file any one company can have as many individuals from the very same database as they like. A field would be created that would simply list all the related ids of the individuals attached to the company. There would be two scripts: one would attach an individual to the company (from the company record) and another would put the individual in a company record (from the individual record). Both have the same effect.

Pete

[Ugo DI LUCA]

Nope,

Your post is clear. I understand that the contact may have a 3/4 line Foreign ID for a relationship to the Company file...

In my opinion though, there are some limits for "scripted relationships" as they cannot offer the width of a real table relationship.

In the case we mentionned above, we would have a contact ID, but what if we wanted to store multiple adresses, phone numbers, related to each company profile.

When John X works for ABC and ABD and ABE, he surely doesn't have the same adress, same functions, phone numbers, email adress, ...

I also tried at first the settings you are about to use. I just can say it's a pain compared to a line item, that you can also use to print multiple reports from.

[Peter Fenner]

Sure, the line item can create the possibility for this depth - and many would require this. I have a contact database for my business with about 10000 contact persons - the number of individuals that are affiliated with more than 1 company is about 50. The number of times this user has private numbers that are different to the companies is 0. The address is the company address. The number that applies across all companies is their cellphone. The number at the various companies is the company number. I do realise that this may well be very different depending on the nature of one's business. I have quick access via a portal to a companies contacts with all key numbers for all contacts readable on one screen.

A layout for contact stores custom numbers (home, etc.), birthday etc.

I wonder how often such depth is required. But as I said, your point is very valid.

Pete

[Ugo DI LUCA]

Pete,

"I wonder how often such depth is required."

I don't know. I've so often been stuck these last month with odd situations as I was redesigning the whole db that I may be going far too deep...

But as I said, it's easier to have a db set for a many to many relationship, with a portal with one field only, than to rebuild the whole thing when it comes to move from one to one/one to many.

About the businesses, I remember you're working in the "Edition" business. Don't you have a lot of freelances contacts that jump from one company to another.

A line-item also keeps the history of the contact...

In the real-estate business, it is likely that Bonnie would have to deal with "Multi-card Agent" (not sure about my translation), once free-lanced, once attached to an agency....

[Peter Fenner]

As I said, your point is very valid - I can't get around that! Bonnie, whose watching us, may very well need such a system. I am curious how you are going to get these records related to properties. Do you see individuals and companies in one database and the line items file just for "storing" multiple addresses, etc. for the individuals allocated to multiple companies? 2 or 3 party databases?

Pete

[broberts7usa]

So much for time to mull things over - you guys are busy on this and keeping my brain hopping.

Ugo, the guy's name was Chad (but Chuck was a good guess). But even though he is a broker and developer and owner and part of multiple companies, he pretty much has the same contact info - otherwise NO ONE would be able to find him.

Also, as to depth: this DB only needs to store pertinent and accurate info about a property. Obviously over time brokers are going to jump from company to company but situations like Chad's are about as complicated as things are going to get. The DB will have to be "checked" or "cleaned" or whatever from time to time, there's no getting around that ... but fortunately it's a small DB (that's why I thought it might be simple? LOL).

Ugo, I am not sure, still, what you mean by a line-item. A portal, that shows all of the person's affiliations, including company affiliations? what kind of a convoluted relationship (or complicated field - you can tell I'm feeling overwhelmed) would be involved in that?

[Peter Fenner]

Hi Bonnie,

Let's say we have an individual and that that individual is related to 20 companies - where are we going to store these 20 potentially different records? We only have 1 individual record - the line item would be another databse that would include the companyID as well as the IndividualID and hence we have 20 new records that can store 20 different sets of numbers, addresses etc. Some companies would probably need such a system, my argument is that for 99% of businesses this is infrequent and a custom layout (on the individual layout) can store any additional numbers. You would need to anyway, for home numbers etc. that are not related to a company.

pete

[broberts7usa]

I think the pre-occupation with multiple addresses, phone numbers, etc. in my case, is overkill. The contact info for a company has to be accurate. The contact info for an individual has to be accurate. But how often do those things change? I don't envision, when finding Chad listed as a broker, having to choose between all his other listings as an owner or manager or developer!

Hmmmm. Maybe the party type is sorted twice. Once by company vs. individual. Another time by broker/owner/architect etc.

Am I completely off track?

The other info doesn't change much. Chad isn't going to have a diff. phone # for every co. he is part of. Usually it's just legal BS anyway, and his address etc. stays the same while the name of the legal ownership might be different for different properties.

[Ugo DI LUCA]

This would be my last answer for the night.

In advance, have good dreams.

I would be glad to discuss this more with you tomorrow (even with other developpers here on the Forum).

Here is how I have things set right now in my files :

A file Company, which I call Operator.db

A file Contacts.db

A file Adresses.db (this may really going too deep here)

Some Contacts acting as individual Operators may be registered in the Operator.db.

That last point is important as a single secretary from your contact.db could one day become a customer in the Operator File, and one Agent may become a Individual Buyer once....

We didn't discussed about the "financial/commercial" part of it, but each operator is given a Account# independantly if he is a customer or a service "supplier".

This is stored in the Account.db, and the balance is made there.

The Account# (Unique_Id) is used for any operational Module (Sale in this case).

[Peter Fenner]

I also need to go to sleep (its 5am here!) but you will need to answer how, when you need to specify the architect in properties databse it is going to be done - the potential entry is in 2 databases (contacts and company). Ofcourse nothing is impossible, but I am sure it is going to be complex.

Sleep on it. Speak to you soon Ugo - Pete

[Peter Fenner]

Bonnie, if you remember in one of my e-mails I said why not consider (at least in some circumstances) to allow users to enter the party in a non-link field. Just a text field on the property layout that can later (during quiet time) be linked appropriately to the parties database.

Just a thought.

Pete

Ugo: I know if I fire back with questions I can keep you up all night. You are truly an addict!

[Ugo DI LUCA]

Ok,

So my last words before I jump into bed. The simplier the better !!

It's true that I'm in the middle of nothing here, just keeping the ideas I've made months to achieve in my proper files, for a very distinctive business application, involving professionals, individuals, foreign distributors, local wholesalers....

And as my contact file is also mainly used for mailings, customer fidelity querries,....I must have things deep (maybe too deep) and up to date.

But start with the simpliest. Anyway, as I said from Pete's sample that is now burried into the thread, this xas a very good starter db. How did we came to go so much in detail by the way ?

Good Night all...

[Peter Fenner]

But you will never (in most circumstances) need the complicated version. Starter database? Final database!

Take care old hand. Pete

[Ugo DI LUCA]

Really live forum :5:12 am here.

You made me come back.

"but you will need to answer how, when you need to specify the architect in properties databse it is going to be done - the potential entry is in 2 databases (contacts and company).

I assume your reaction is about the Account and Operations.

Well, the Property db is the Product. I can store the info from the Company file or Contact there.

When I'm selling the Product, I'm not anymore dealing with the architect but with the buyer and the seller.

The architect would come by lookup from the Product (Property), except if he is the seller (then I'd catch his account# !)

And the company file has a direct portal to the Account.db, listing all commercial operations...

[Peter Fenner]

In bonnies property database there are times, many times, where one entry can either be a contact or it can be a company. I know one could resolve this, but its going to get "heavy".

You follow my gist?

I knew you would be back! You just cannot resist! I like that. Pete

[broberts7usa]

Ugo always stretches the mind. (Good night Ugo! what a workout!) I can see how this works well for him (and his company). It makes perfect sense, after having seen his "Operator" file. I think his solution is nearly perfect for his scenario.

But I don't need this level of complication! This DB will never ever be an invoicing DB. It's going to generate vacancy rates. And also, it will help the brokers with their deals, so they can have more property info at their fingertips. This DB "might" eventually be relied upon for contact info. This DB "might" eventually be relied upon for contact info history, using OLE embedded icons to attach pertinent documents. But not now. All this going round and round about how to deal with the Parties DB is making me crazy. Each party is either a person or a company. They can also be an architect or a developer or a management agent or a broker. Or their company can be. All of this can be displayed through portals. Is this really that hard? Or am I really thick? I don't expect it to be totally easy, but, really, I was hoping things might be less complicated.

I hope you will forgive this very frustrated post. And also I thank Pete and Ugo for all their intense work on this!

[broberts7usa]

I can't believe you guys are still at it, while I was typing all that ...

[Ugo DI LUCA]

We're all ma here, coming to discuss with americans at 5:23 am. And Chrstian is here also, following is dar code dilemna

Where are we going ? I like that too. Just the breakfast is hard to digest with those cigarettes in the ash tray...

[Peter Fenner]

Bonnie, do you understand my solution? Have you seen it yet? If yes, initial feedback would be welcome. What don't you like about it. It really is as easy as you are going to get (other than not storing any party info).

Pete

[Peter Fenner]

But Americans rule the world (you live in France, you know all about that)

Wait for an aussie to butt in now!

[broberts7usa]

At this point the americans (l'americana) are just listening to the hyperactive europeans going at it, 6 hours later (5:30am) when they should be asleep! Christian is here too? this should be good ....

[broberts7usa]

Peter I DID see your solution and I think it's super! I like it very much especially in light of being very overwhelmed by all this going back and forth (which means I haven't had time to tell you so). Also, I've been trying to find the time to have a look at Russell's namefinder (he'll be here next, and who knows what Christian might throw into the mix, he'll be sorry he found this thread) ...

phew you guys wear me out.

[Peter Fenner]

Bonnie, I am not looking for praise (hah!). No seriously, when you have the time I would rather know if it fundamentally does the job? Is it still too complicated for your requirements, etc. When you've had time to summise over all the bombardments let me know. Be brutal. Pete

[Wendy T]

hi all

just butting in cos i'm an aussie!

now you really all should be asleep. it's a reasonable hour here (nearly 2pm) but what's your excuses?

on a more serious note, i'd like to be having a look at what you're all talking about but i can't download it. i work on an all-windows network (afaik) so haven't been able to look at any of peter's uploaded samples. doh!

cheers,

wendy

[Peter Fenner]

Hi Wendy

I am pleased to see that, unlike so many other Aussie's in the forum, you have include a real picture of yourself.

You should still be able to open it, but I will get a file from my Mac onto my PC, and if you provide me your e-mail address I will send you a zipped version.

Send your e-mail address to me at [email protected] I will send it when I am up in the morning, but right now I am off to sheep. Sorry! sleep.

Pete

[broberts7usa]

Wendy, I'm Bonnie and I started all this :. I'm still up (but not for long, like 5 minutes, I'm on the East Coast US. I'm bleary-eyed and it's only just after midnite. Never mind learning FM, I have a job and a couple of kids and a couple of houses, and I don't know how these guys in Europe do it - they must have good women, right?)

Here's Peter's file:

http://www.fmforums.com/threads/showthreaded.php?Cat=&Board=UBB17&Number=67712&page=&view=&sb=5&o=&vc=1

Glad to have Aussies on board, especially women! (simmer down, you guys, I'm not trying to be inflammatory) Your take will be welcome!

I am off to sheep too (just saw Peter's post).

Bonnie

[Peter Fenner]

Wendy, ignore my post - I have emailed you the files. I hope they work now.

Anyway, Good night/morning everyone - See you all again soon.

Pete

[Peter Fenner]

Further to the key discussion above I have attached an updated version of possible solution. Remember, the key issue here is the ability to be able to bring parties into another database (properties). These parties are either companies or individuals (from 2 databases). Whereas the previous solution required a separate layout for creating a link to parties, this update provides a mechanism on the same layout. The mechanism is disabled when a party has been selected. Removing such selection provides the "selection' mechanism again. As before, I would like input as to any potential improvements to the fundamental structure of such mechanisms. (Download format: .sit)

Pete

[broberts7usa]

Peter, you always sound so formal when you're offering up your solutions (examples)! We don't bite ... often ...

The other key here, everyone, is he's using 2 (TWO!) other files for parties (companies vs. individuals) - this was a major part of our discussion above - and a major part of my decision for my main DB and ER rationale.

The above download link doesn't work for me (Windows user). Peter you already emailed the files to me so I have them, thanks, but you might want to use a .zip format? instead of .sit? so maybe everyone else can see? Not sure why the download doesn't work for me. Is it working for everyone else?

It's not Thursday yet (my day to work solely on FM - it's been a hellatious week) but since I can't stay away from this I'm working into the night and will get right back to you about how it looks (first glance = SUPER). Of course I can never match Peter and Ugo for staying up until the birds are singing - but I've got about 4 hours in me.

I vote for a new thread, this one's getting unwieldy. 5 pages! If someone starts one before I get back, please let me know where it is and what it's called ...

(delving into Pete's files) Be back atcha in a couple - Bonnie

[broberts7usa]

I've been looking at Peter's files and I'm sending comments to a new thread. I'll put it in "relationships" and I'll call it "multiple DBs 2". Hey, I think we're getting to the point where everyone agrees there needs to be 2 databases for people vs. companies, to feed into the main DB ...

[Peter Fenner]

I have started a new thread with the files attached in zip format. New thread: "one DB multi relationship (Thread 2)

[broberts7usa]

take that back, Peter beat me to it (shame on you Pete for not telling us) - it's called "one DB with multiple relationships thread 2" - Bonnie