Ok.. I'm getting somewhere.. I just can't decide, where to record
responses to campaigns (i.e. event attendance)
Quote:
Based on what you describe, I think the relationships will look
something like this.
>
BUSINESS/CUSTOMER----(1,M)---CONTACT---(1,M?)---INVITATION(attendance
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
