Connecting people with multiple companies

[Wille]

Hi,

I am quite new to this so excuse me if this is simple.

I am purpose building a database.The focus is on individuals who attend different events. that relation is not a problem. The individuals are as well related to companies. If i go to the company file it collects and calculates the information about the employees visits to these events and show a summary for the company.

Now the problem is that some people work at multiple companies. How do i construct that relation. Basically what i need is that the company field can store multiple values that are related to unique values.

And as well how do I avoid duplicating companies with a portal use here?

I would be more than happy for any advice.

Wille

[bcooney]

Welcome to the forums, Willie.

I would model this with the following tables:

People

Companies

PeoComp

Events

PeoEvent

Since people can attend more than one event, you need what's called a join table that captures those combinations. Likewise, since a person can be associated with more than one company, you also need a join table, "PeoComp," that captures those combinations.

In PeoEvent, I would have the PeoID, EventID. If a person is representing just one of his companies at this particular event, you could also capture that CompanyID in PeoEvent.

Use PeoComp as the basis of a portal on your People form. Here you could also include a position title, and perhaps DateStarted, etc. anything that's exclusive to that person's employment at that company.

Reporting is a bit tricky, because if a person represents more than one company, how will you count them from the view of a company? You'll be counting them twice, unless you specify just one of their companies at the event.

[Wille]

Hi, thanks for the input,

i believe i found a much smoother way of doing this. there is big chance I'm wrong but this is what i made instead.

The people that are going in to the database are journalists, reason for several companies, as you mention the problem as well is for what company did they attend. The whole purpose here is to be able to look at a journalist and see what he or she has done and as well for a newspaper.

I made three tables, Journalists, Companies and Events.

The first two tables have their specific info and the third one is sort of a list.

It lists date, name, company, event and specific info about the visit. Then I related the name with the name of the journalist and company name in the same way. This worked perfectly. At the journalist layout a portal collects the info about the events that person has done and for which companies, the same for companies.

This works almost perfectly except a small detail in the portal. Lets say I have person John Smith, and I want to add an event for him, I do that, and the company he attended for already exist I create a duplicate of that company. This can be solved by not letting the event table create posts in the company table, but that is not a good solution since there will be new companies coming so to speak, and then I would have to first create a post for the company, and then adding his record for the event, or the other way around.

So is there a way that I create new posts in a table through a portal If the name or particular info doesn't exist, otherwise not?

[Wille]

Or is this just the wrong approach? I have not created databases before, but it seems to me that this should be sufficient.

[bcooney]

Wille,

I still believe that my model will prove to be more useful. Without a table of Events, you will not be able to collect all attendees at any one Event.

AND...do NOT relate tables using text values such as names! Use autoenter serial number IDs. Each record in the table should have a unique ID.

-Barbara

PS: I'm off to work, will have more time later tonight. Anyone else is welcome to help out.