Beginner needs advice

[countrylife]

I am struggling to work out how many tables I will need to build an events management database for a charitable organisation. Excel has been my system of choice but is messy when it comes to trying to organise all the information because I have to create a spreadsheet for each event and then enter in the attendees etc.


I have a list of members with their first names, last names, postal addresses, post codes, telephone numbers, email addresses.
I have a list of events with event names, location, date, time, ticket prices.
I have to include options such as coach pickup points and times. Whether lunch is required or not (yes/no).
If an event involves an overnight stay then options such as single room supplement or not.
Details of how many tickets a member has purchased and how much paid.
Date when cheques are paid into the bank together with the paying in slip's folio number.
Ability to produce a report as to who has booked for which event/events. How much they have paid.
Income from each event.

I have searched for a template but cannot find one suitable.

Advice to get me started on the correct path will be most gratefully received.

[aytee111]

Make a list of all the fields that you need, just the heading. Then put them into logical tables - one subject per table. Draw it on paper with lines joining up the indexes between tables.

- events - event name
- members - member name, dob, address, etc.
- information for a single event - date held (from/to), location, ticket price, lunch (will include event ID)
- information of members attending that event, room required, number of tickets (will include event ID, member ID)
- payment transactions (will include ID of prior table)

[ranman256]

something like:

tMembers table
------------
MemID (auto, for db use only)
LastN
FirstN
OrgID (string if using internal organization id #)
addr
city
state
etc..


tEvents table
------------
EventID (auto)
EventName
EventPlace
EventDate
LunchReq (y/n)
PickupPt
PickupTime
etc


tPurchases table
------------
PurchID (auto)
MemID (long) [tMembers reference]
EventID (long) [from tEvents table]
PurchDate
PurchAmt
AmtPd
PaidHow (check,visa,paypal)
TransID


tPurchaseDtl table
------------
PurchDtlID (auto)
PurchID (long)
PurchItem
ItemCost
Qty


tPayments table
------------
PayID (auto)
PurchID (long, tPurchases table ref)
PayDate
AmtPd
PaidHow
TransID

[countrylife]

Many thanks both of you for your speedy replies. I will see, this weekend, if I can put it into practice!!

[countrylife]

I am still confused when it comes to creating the relationship between tables but feel that I need to keep trying to work it out myself. However one question: do I need to add separate fields in the Events table for each additional coach pick-up point (when there will be more than one "pick-up" point) or should I create a separate table for this category?

[orange]

You may find the info/links here to be helpful.

It all starts with a clear description of the business you are trying to support with this proposed database. Write a clear description in simple plain English. Review it with colleagues to ensure you have the essence of WHAT you are dealing with.

If you are new to database, then the links I mentioned should be helpful. Also, there are free videos by BA-Experts on youtube called "knowledgeknuggets" that overview the concepts of analysis and design and related tools/methods. These are humorous, short and really hit the essential concepts.


There are free, generic data models at Barry Williams' site
Here is one related to Event Planning.
Good luck.