View Single Post
  #4  
Old June 4th, 2010, 10:15 PM posted to microsoft.public.access.tablesdbdesign
Steve[_77_]
external usenet poster
 
Posts: 1,017
Default Help needed with table design/layout for newbie

I suggest putting -numMax (maximum places available on the course)
and -curPrice in TblSession so if either changes from one session to
another, you have a place to record it.

As far as tracking who has paid, a yes/No field in tblSessionMembers should
suffice.

Steve



"jase118" u60596@uwe wrote in message news:a90a1aff03e1a@uwe...
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?