Need Advice

[Stranger]

Hi, I'm creating a database for a division of the company that has started up. We are a non profit.

There will be customers, different membership levels and activities that each customer will attend. I need to be able to track customer data, payments, activities each customer attended and be able to track attendance.

Here is what I have so far 4 tables:

tblpayments consist of:

PaymenttID Payment amount: Payment date: Payment Method: Credit card number: Name as it appears on credit card: Type of credit card: Expiration date: Payment authorization number: Security code: Customer ID



tblcustomers consist of:
Customer ID First Name Last Name Dear Address City State/Province Postal Code Region Country/Region Company Name Title Work Phone Work Extension Home Phone Mobile Phone Fax Number Email Name Birthdate Last Meeting Date Customer Type ID Referred By Notes Spouse Name Customer's Interests SSN JudsonID


tblcustomertypes:
CustomerTypeID CustomerType


tblactivities:
ActivityID CustomerID ActivityDate ActivityTime Subject Notes


For relationships I have the following:

Customers.customerid 1->many tblpayments.customerid
Attributes: enforced

customertypes.customertypesid 1->many cusotmers.customerstypeid
Attributes: not enforced, right join

customers.customerID 1 -> many activities.activitiesid
Attributes: Enforced, cascade Updates, cascade deletes


Am I on the right track? Do I need another table to track attendants or would activities table be able to do that with the current setup?

Thanks for any help.

Jack

 

[[MVP] S.Clark]

I would have a conference attendees table. ConferenceID, PersonID, etc.

When a person attends the event, it is probably of interest to record who
the customer worked for at the time that they attended, as well as other
point in time information may be pertinant at the time. Many attendee in a
particular industry will jump from company to company, so it would be nice
to know that they attended a specific conference when they worked at a
particular place.

It is probably not a good idea to store the credit card numbers and
expiration dates. Too risky if you get hacked, loose a laptop, etc.

--
Steve Clark, Access MVP
FMS, Inc.
www.fmsinc.com/consulting
==========================
Hi, I'm creating a database for a division of the company that has started
up. We are a non profit.

There will be customers, different membership levels and activities that
each customer will attend. I need to be able to track customer data,
payments, activities each customer attended and be able to track attendance.

Here is what I have so far 4 tables:

tblpayments consist of:

PaymenttIDPayment amountayment dateayment Method:Credit card number:Name
as it appears on credit card:Type of credit card:Expiration dateayment
authorization number:Security code:Customer ID



tblcustomers consist of:
Customer IDFirst NameLast NameDearAddressCityState/ProvincePostal
CodeRegionCountry/RegionCompany NameTitleWork PhoneWork ExtensionHome
PhoneMobile PhoneFax NumberEmail NameBirthdateLast Meeting DateCustomer Type
IDReferred ByNotesSpouse NameCustomer's InterestsSSNJudsonID


tblcustomertypes:
CustomerTypeIDCustomerType


tblactivities:
ActivityIDCustomerIDActivityDateActivityTimeSubjectNotes


For relationships I have the following:

Customers.customerid 1->many tblpayments.customerid
Attributes: enforced

customertypes.customertypesid 1->many cusotmers.customerstypeid
Attributes: not enforced, right join

customers.customerID 1 -> many activities.activitiesid
Attributes: Enforced, cascade Updates, cascade deletes


Am I on the right track? Do I need another table to track attendants or
would activities table be able to do that with the current setup?

Thanks for any help.

Jack

 

[Stranger]

Hi,

I should clarify. The customers will not be changing unless they leave.
the division is a wellness program so the activities would be things like
therapy in the pool, aerobics, things like that. Only two people will be in
the database. They don't seem to be worried about who taught the class at
the specific time.

Does this help any?