event/contacts/attendees

[tess]

I've created a small database with the purpose of tracking events, who
is invited, who attends, and all related information.

I"ll try to explain here what I've done so far and where I need help.

Current Customers:
I have businesses, with a unique id and contact for the business with a
unique id. A business could be invited multiple times per event, but
separate contacts.

A business can be invited to several event, each time could be the same
contact, or different contacts invited.

The first thing I need to now is, how do I go about 'attaching' these
invitations to an Event?

The invitation list and all of it's information is pulled from DBSi and
I can import it into my database easily. I assume I need a
tblCustomers with keys being combination of customer and contact id #s,
I can then import the invitation list to that table.

If that combo person/business already exists, it won't add it again.

SO, now how do I 'attach' these to a specific event? Each event of
course has a unique id.

I will then have to somehow track 'responses', which could mean they
attended an event, responded to a mailer with a phone inquiry or
several other different things.

I'm just lost and need some direction..

I'm sure anyone willing to help will have questions.. feel free.. and
thanks in advance

[pietlinden]

Based on what you describe, I think the relationships will look
something like this.

BUSINESS/CUSTOMER----(1,M)---CONTACT---(1,M?)---INVITATION(atte ndance
response)---(M,1)---EVENT

IOW,
Each Contact belongs to only one Business. Each Biz can have many
contacts. Each Contact can receive many invitations, but only one for
each event. (so EventID, ContactID combination is unique in
Invitation). There's only one response per invitation, right? If so,
you can put that in the invitation table. Otherwise, you'd need a
child table of Invitation (InvitationID as FK as minimum). Then you'd
have a table of events. If you can get multiple responses for an
invitation, your schema would be slightly different... the relationship
between Invitation and Response would be 1,M. At present, I have it as
inherently 1:1, because it's still in the Invitation table. Breaking
it out would yield this:

The only difference would be the PK for Invitation. If a contact can
have multiple invites per event, your PK is something other than the
(EventID, ContactID) combination, like an Autonumber. (Then that
record can have child records.) otherwise, your PK is (EventID,
ContactID).

I would test the design by creating some tables, adding a few sample
records, and writing some queries to test your design. Once you have
that, (ie. you get the info out that you need), build forms etc.

HTH,
Pieter

[tess]

Ok.. I'm getting somewhere.. I just can't decide, where to record
responses to campaigns (i.e. event attendance)

Would I put a response Field in the table you refer to below as
invitation?

The table would have a customer id, contact id, event id and a response
field??

pi********@hotm ail.com wrote:

Based on what you describe, I think the relationships will look
something like this.

BUSINESS/CUSTOMER----(1,M)---CONTACT---(1,M?)---INVITATION(atte ndance
response)---(M,1)---EVENT

IOW,
Each Contact belongs to only one Business. Each Biz can have many
contacts. Each Contact can receive many invitations, but only one for
each event. (so EventID, ContactID combination is unique in
Invitation). There's only one response per invitation, right? If so,
you can put that in the invitation table. Otherwise, you'd need a
child table of Invitation (InvitationID as FK as minimum). Then you'd
have a table of events. If you can get multiple responses for an
invitation, your schema would be slightly different... the relationship
between Invitation and Response would be 1,M. At present, I have it as
inherently 1:1, because it's still in the Invitation table. Breaking
it out would yield this:

The only difference would be the PK for Invitation. If a contact can
have multiple invites per event, your PK is something other than the
(EventID, ContactID) combination, like an Autonumber. (Then that
record can have child records.) otherwise, your PK is (EventID,
ContactID).

I would test the design by creating some tables, adding a few sample
records, and writing some queries to test your design. Once you have
that, (ie. you get the info out that you need), build forms etc.

HTH,
Pieter

[pietlinden]

te**@conwaycorp .net wrote:

Ok.. I'm getting somewhere.. I just can't decide, where to record
responses to campaigns (i.e. event attendance)

Would I put a response Field in the table you refer to below as
invitation?

The table would have a customer id, contact id, event id and a response
field??

It depends on whether you need to store multiple responses or not. If
you need to store one per letter or whatever, then store it in that
table. Otherwise, store it in the invitations table.