View Single Post
  #3  
Old June 4th, 2010, 09:49 PM posted to microsoft.public.access.tablesdbdesign
jase118
external usenet poster
 
Posts: 4
Default Help needed with table design/layout for newbie

jase118 wrote:
Hi, i have just joined the forum and have built a few DB's in the past but
found i have forgotten almost everything!

[quoted text clipped - 41 lines]
Cheers
Jase


just received this from another forum, what do you think as a start point?

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.



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?