Client and Event Database

[kattzndagz]

Hi everyone

Previously I posted a similar questions and I only had Access 97 to use. Now i have Access 2007 and I am still having trouble. I know I am missing something simple but I just can't see it.

The templates Microsoft has are not what I need at all and I can’t download any other samples due to my employers security.

We have clients (could be companies) with 1 or more contacts within that company.
We have events every year like Xmas party, midyear function etc
Clients can be invited to any or all of the events

They had a very simple database previously where they would delete the previous years data and start again, however, I want to be able to keep all the data. So I will be creating reports.

TABLES
TblClient
PK ClientID
CompanyName
Address
etc

TblContact
PK ContactID
FK ClientID
FirstName
LastName
LOOKUP RSVPType (by default, if this is blank, they have not responded)
etc

TblRSVP
PK RSVPID
RSVPType (these are yes or no)

TblEventDetail
PK EventDetailID
FK ContactID
LOOKUP EventType
EventDate
EventTime
NoofPlaces
NoofInvitationsSent
etc

TblEventType
PK EventTypeID
EventType (xmas, midyear etc)


Events also have their costs so I would like to record that too, so I have;

TblCompanyDetails
PK ServiceProviderID
Company Name
ContactName
Address
Etc


TblServiceCost
PK ServiceCostID
FK ServiceProviderID
ServiceProvided
ServiceCost
Notes



I thought about having a FrmEvent based on TblEventDetaill with subforms in tab controls for invitees and service provided.

As I am typing this, I am thinking that it might be better just to have the base form with buttons to take me to the other forms? What are people’s suggestions for this and the database as a whole?

The FrmEvent will also have a couple of unbound controls so I can add formulas for counting up how many are coming, who hasn’t replied etc.

How do I select the contacts to invite to the various events? this what I'm really having trouble with

Thanks

 

[johnherman]

I would put the RSVP, the accept/decline/tentative decision and date in the TblEventDetail rather than separate. That makes reporting a little easier, and the RSVP is definitely related to the event, so from a relational design perspective, it's the right place for it.

As far as who to invite to what event, I would consider a picklist for the event and select from the Clients and then within the Clients for the Contacts.

Otherwise, the design looks good.

====================================
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[Thargy]

Kattz,

your tables are a fair start, but I think they can be improved upon.

Naming conventions
First of all, I suggest that you get rid of the 'tbl' prefix. In a database it's reasonable to assume that everything which one is selecting from is a table, unless stated otherwise.
I also suggest that you use upper case names with spaces replaced by underscores. This makes not a shred of difference to Access, but does make it easier to type names correctly.
Also, adopt either surrogate primary keys, but use names to indicate function.

So, your table names could be

CLIENT
CONTACT
EVENT_DETAIL
EVENT_TYPE etc.

With the exception of the primary key for each table, make the names meaningful and don't abbreviate just for the sake of it. So
TblEventDetail
PK EventDetailID
FK ContactID

YOu need an address table for all addresses.
Never separate dates and times, a date includes time anyway, and the type works correctly in queries.
LOOKUP EventType
EventDate
EventTime
NoofPlaces
NoofInvitationsSent

would become

EVENT_DETAIL
EVENT_DETAIL_ID
CONTACT_ID
EVENT_TYPE
EVENT_TIMESTAMP
NUMBER_OF_PLACES
NUMBER_OF_INVITATIONS_SENT

Because NUMBER is frequently abbreviated to NUM, and description to DESC, the last two fields could just as well be
NUM_PLACES
NUM_INVITATIONS


Since the RSVP is a true false flag, don't create a separate table for it, add it to the table as a boolean field.
So,

TblContact
PK ContactID
FK ClientID
FirstName
LastName
LOOKUP RSVPType (by default, if this is blank, they have not responded)

would become

CONTACT
CONTACT_ID (autonum primary key
CLIENT_ID (foreign key to the client table
FIRST_NAME (character field)
LAST_NAME (character field)
HAS_RESPONDED (boolean yes/no field)

Regards

T

 

[johnherman]

While I agree that RSVP is a Boolean, the results are not. In addition to people who do not respond, there are those who respond with a YES, NO, and Tentative. In addition, the column could be used to track NO-SHOW, which would be a YES updated after the fact for someone who did, in fact, not attend. So, rather than a Boolean, I recommend a one-byte character. While waiting for a response, the Client Event Status, or whatever you call it, could be set to some value such as W for Waiting for Response.

====================================
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[ChrisHunt]

I wouldn't rush to follow much of Thargy's advice regarding naming conventions - they're a pretty personal thing, unless you're constrained by some site standard. So long as you have an approach that makes sense to you, and you follow it consistently, you'll be fine. Problems start when you start mixing things up - underscores in one table, camel case in another, etc. Access won't care, but you'll never remember what to use where!

Having said that, personally I wouldn't use the word "details" in a table name unless it was part of a master-detail relationship, eg: tblOrder and tblOrderDetails. But like I said, it's a personal choice thing.


Here's what I would do, starting with the main entities:

Event
PK Event ID
Title
Address
Type
...

Client
PK Client ID
Company Name
Address
...

ServiceProvider
PK Provider ID
Provider Name
Address
...

Note that I again differ with Thargy over having a separate Address table. If you've got a relatively small number of addresses to deal with, and they aren't shared much between entities, it's not really worth it. The only exception you might consider is building a Venues table for the venues where your events take place - as I expect you re-use those quite a lot.

OK, let's add in the client contacts:

Contacts
PK Contact ID
FK Client ID
Title
Forename
Surname
Phone
Email
Preferred Contact Method
...

Now to tie it all together:

Invite
PK Invite ID
FK Event ID
FK Contact ID
Sent Date
Response Date
RSVP Status
...

Service
PK Service ID
FK Event ID
FK Provider ID
Service Type
Cost
...

Tables like this, with two foreign keys, are used to resolve many-to-many relationships between tables. So an event has many contacts, and a contact has many events - the invite table tracks who is invited to what.

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd