Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-04-2010, 10:05 AM   #1
jas118
Registered User
 
Join Date: Jun 2010
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
jas118 is on a distinguished road
Question advice needed for table layout/design for a newbie

Hi, i have just joined the forum and have built a few DB's in the past but found i have forgotten almost everything!
I do remember with a previous DB i layed the tables out incorrectly which impacted on the flexibility down the line so would like advise before i start in order to get a good foundation.
My dB is for our watersports centre to book people onto courses and retain their details in a contacts table to be imported to Outlook for mailing info.
We run various courses which then run on multiple dates across the year. So for example we may run a Powerboat Level 1 course, this then runs 4-6 jan, 4-6 Feb and so on. I then need to add customers to each course date (i want to retain the customer information for use on email or telephone marketing etc)
This is the basic concept, the additional functionality i require is:

-Add/ Remove courses - add/remove course dates

-view courses by type or date (ie view all power boat courses, or all courses for January or all powerboat courses for January)

-view available spaces on courses (same search criteria as above)

-Add/ Remove people off courses

-Print bookings (same search criteria as above)

-view reserve space bookings (people who can fill cancellation slots on certain courses at certain dates)

-View payments received /outstanding payments

This is my plan for the way i want it to work! I now need to start building the tables.
For the contacts table i plan on using the same format as Outlook contacts so i can import directly into outlook for emailing customers (i would be happy to change this especially if i could send mails direct from access into Outlook, rather than importing a contacts list every-time i add a new name)
I am very open to suggestions here as i am finding the first step a bit daunting and dont want to waste lots of time making a DB that wont do what i want a bit later down the line.
I have tried to give as much detail as i can, if you want to know anything else please ask.

I will be grateful for any help with this

Cheers
Jase

jas118 is offline   Reply With Quote
Old 06-04-2010, 10:44 AM   #2
jzwp22
Access Hobbyist
 
Join Date: Mar 2008
Posts: 2,629
Thanks: 0
Thanked 311 Times in 308 Posts
jzwp22 has a spectacular aura about jzwp22 has a spectacular aura about jzwp22 has a spectacular aura about
Re: advice needed for table layout/design for a newbie

Welcome to AWF!

You can use Access automation with Outlook to send out e-mails, but that is down the road at this point. The table structure is the most critical aspect.

You'll need tables for members and courses

tblMembers
-pkMemberID primary key, autonumber
-txtFName
-txtLName

tblCourses
-pkCourseID primary key, autonumber
-txtCourseName

You'll also need a table for the course dates or sessions

tblSessions
-pkSessionID primary key, autonumber
-dteStart (session start date)
-dteEnd (session end date)
-fkCourseID foreign key to tblCourses

Now you'll need to associate the members with the applicable session

tblSessionMembers
-pkSessionMemberID primary key, autonumber
-fkSessionID foreign key to tblSessions
-fkMemberID foreign key to tblMembers

In order to determine if there are vacancies in a particular session, you will need to know the maximum # of students/session or course. If a session is limited by its location, then the field for the max # belongs in the session table. If the max # only depends on the course, then it belongs in the tblCourses. The same would be true for the course fee. If the fee is dependent on the session (you might charge less for sessions conducted in the off season versus those conducted during the season), then the field belongs there. If the fee is just dependent on the course, then it belongs in tblCourses.

As to payments, are you only tracking payments for the courses/sessions or are you also tracking membership dues etc.?
jzwp22 is offline   Reply With Quote
Old 06-04-2010, 12:42 PM   #3
jas118
Registered User
 
Join Date: Jun 2010
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
jas118 is on a distinguished road
Re: advice needed for table layout/design for a newbie

My Table layout so far:

tblMembers
-pkMemberID primary key, autonumber
-txtFirstName
-txtLastName
-txtAddress1
-txtPostcode
-numTel
-numMobile
-txtEmail

tblCourses
-pkCourseID primary key, autonumber
-txtCourseName
-numMax (maximum places available on the course)
-curPrice

tblSessions
-pkSessionID primary key, autonumber
-dteStartDate (session start date)
-dteEndDate (session end date)
-fkCourseID foreign key to tblCourses

tblSessionMembers
-pkSessionMemberID primary key, autonumber
-fkSessionID foreign key to tblSessions
-fkMemberID foreign key to tblMembers


As for tracking who has paid, would i be right to put that under tblSessions with a yes/no option?
I am not sure how to setup a foreign key, went into relationships and setup a 1 to many link, is this correct?

jas118 is offline   Reply With Quote
Old 06-04-2010, 12:56 PM   #4
jzwp22
Access Hobbyist
 
Join Date: Mar 2008
Posts: 2,629
Thanks: 0
Thanked 311 Times in 308 Posts
jzwp22 has a spectacular aura about jzwp22 has a spectacular aura about jzwp22 has a spectacular aura about
Re: advice needed for table layout/design for a newbie

Quote:
As for tracking who has paid, would i be right to put that under tblSessions with a yes/no option?
No, the tblSessions is not the correct place since the session can apply to many members. The tblSessionMembers is the appropriate table since that is where you tie specific members to the session in which the course is offered. Using a yes/no field will not allow you to track partial payments, only full payments.

tblSessionMembers
-pkSessionMemberID primary key, autonumber
-fkSessionID foreign key to tblSessions
-fkMemberID foreign key to tblMembers
-logPaid (yes/no)

Quote:
I am not sure how to setup a foreign key, went into relationships and setup a 1 to many link, is this correct?
The fields identified with the "fk" prefix are the foreign key fields, they need to be long number datatypes to correspond to the primary key fields (autonumber datatype) of the main table that they are related to.

The foreign key should be on the many side of a one-to-many relationship, so if that is what you did in the relationship window, then you would be correct.
jzwp22 is offline   Reply With Quote
Old 06-04-2010, 01:42 PM   #5
jas118
Registered User
 
Join Date: Jun 2010
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
jas118 is on a distinguished road
Re: advice needed for table layout/design for a newbie

I have put payment and joining instructions under session members:

tblSessionMembers
-pkSessionMemberID primary key, autonumber
-fkSessionID foreign key to tblSessions
-fkMemberID foreign key to tblMembers
-logPaid yes/no
-logInstructionsSent yes/no

I am reading up on relationships, relational database principles and normalisation before i add to much more
jas118 is offline   Reply With Quote
Old 06-05-2010, 01:01 AM   #6
jas118
Registered User
 
Join Date: Jun 2010
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
jas118 is on a distinguished road
Re: advice needed for table layout/design for a newbie

i have done my reading and i have attached a picture of my tables and the relationships i have setup. I have attempted to normalize it and as a result have created new tables.
Let me know what you think.
Attached Images
File Type: jpg tbl_relation.jpg (59.4 KB, 152 views)
jas118 is offline   Reply With Quote
Old 06-05-2010, 03:45 AM   #7
jzwp22
Access Hobbyist
 
Join Date: Mar 2008
Posts: 2,629
Thanks: 0
Thanked 311 Times in 308 Posts
jzwp22 has a spectacular aura about jzwp22 has a spectacular aura about jzwp22 has a spectacular aura about
Re: advice needed for table layout/design for a newbie

What is the "isAvailable" field in tblCourse?

Also, in the member table, it looks like you have 3 phone number fields of different types. If a person has more than one phone number, it describes a one-to-many relationship.

tblPhoneTypes
-pkPhoneTypeID primary key, autonumber
-txtPhoneType

tblMemberPhones
-pkMemberPhonesID primary key, autonumber
-fkPhoneTypeID foreign key to tblPhoneTypes
-txtPhoneNumber

jzwp22 is offline   Reply With Quote
Old 06-05-2010, 10:40 AM   #8
jas118
Registered User
 
Join Date: Jun 2010
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
jas118 is on a distinguished road
Re: advice needed for table layout/design for a newbie

the isavailable field i put is as i was advised on another forum. It is so i can make courses available or unavailable.
I have changed the phone fields.
Thanks

Last edited by jas118; 06-05-2010 at 10:52 AM.
jas118 is offline   Reply With Quote
Old 06-05-2010, 02:49 PM   #9
jzwp22
Access Hobbyist
 
Join Date: Mar 2008
Posts: 2,629
Thanks: 0
Thanked 311 Times in 308 Posts
jzwp22 has a spectacular aura about jzwp22 has a spectacular aura about jzwp22 has a spectacular aura about
Re: advice needed for table layout/design for a newbie

You're welcome.

I thought you might have been using the IsAvailable field to determine whether the maximum number of students had not yet been reached for a particular session. You can do that with a query.

jzwp22 is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Advice needed on DB set-up & Normalization for Novice AccessWomble Tables 3 12-04-2009 09:15 AM
relationship advice needed :-) sistemalan Tables 3 11-30-2009 02:45 AM
Learning path advice needed thart21 General 1 01-24-2003 01:37 PM
Is a new table needed? Advice needed sueviolet Tables 4 09-11-2002 10:54 AM
Newbie help needed Xerox Queries 2 06-05-2002 07:11 AM




All times are GMT -8. The time now is 09:18 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World