Managing Contacts

[William Mead]

Hi, I hope someone has some good advice here for this one:

I work for an organization that runs two large trade shows each year. Currently our FMPro database only has one contact person, one phone number and one email address for each company we deal with for both shows. This is adequate for 85-90% of the companies we deal with who have the same contact info for both shows.

It is also helpful, as we do a lot of exporting to excel for others to have just one set of fields to get. There are a number of people using the database that are not very proficient with databases/computers. So simplicity is key.

I am rebuilding the database from scratch and I am considering changing this structure because it just makes us look like amateurs when we can't efficiently keep track of more than one person for a particular company. Multiple fields per record seems messy, and would cause problems when exporting data.

I know I could keep a table of contact people and access it via a portal. But what about when we export data under these circumstances?

Does anybody have some good ideas about how to manage this kind of situation? It seems to be a classic case of more features = more complexity, but I am wondering if there is a good, elegant, simple solution out there somewhere. Any suggestions are appreciated.

[comment]

Something like this, perhaps? Note that this assumes one contact per Company per Show. Otherwise you'll need another join table between Registrations and Contacts

[Ender]

I know I could keep a table of contact people and access it via a portal. But what about when we export data under these circumstances?

If there's only one one-to-many relationship involved (or one join table), the records can be exported from the many side (or the join table), and include fields from the parent table(s).

[Ender]

Hey comment,

Looks good except I'm not sure that the Registration table would need to link directly to both the Company and the Contact. This might be done as part of the interface (to select which Contact is assigned to a Registration), but structurally it really only needs to be related to Company (if a Company being registered implies that all of its Contacts are registered) or to Contact (if each Contact is registered independently).

I'd probably link Registration to Contact and not link Registration to Company. This way the system can track Companies and their Contacts over time, and a Contact can be registered for different events, but not necessarily all events that that company has participants in.

[comment]

That's the problem with skimpy descriptions - everybody uses their own imagination to complete the missing details.

The way I understood this, the Acme Company reserved a booth at the Summer 2007 Space Show. The person to talk to about this at Acme is Adam. The same company also reserved a booth at the Winter 2007 Vacuum Convention, and the contact for this is Betty.

True, Adam is a contact for Acme, so you could know it's Acme's registration by this. But it's not 'right': the registration is properly made to the company. To illustrate, suppose a registration is made, but the contact is not determined yet. Here you would have a half-orphaned record.

[William Mead]

Yes, Comment, your assumption about what I am thinking is correct. I apologize for not being more clear to begin with.

Thanks to both of you for your ideas. I am continuing to chew on it.

[Ender]

You're right, it probably depends on the way they do things. Anyway, it's good to hear your reasoning. Makes sense to me.