Help needed with table design/layout for newbie

[jase118]

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

 

[jase118]

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

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.

 

[jase118]

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?

 

[Steve]

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
(e-mail address removed)

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?

 

[Jörn Bosse]

When you setup relations, the fields need the same type: both integer
values. As it seems, your tblSessionMembers is the table to create the
m:n-relationship between members and sessions. Thats why you have to put
your yes/noe-field into this table. If you realise it that way, you can
directly see who has paid by selecting the session, or which session a
user has already paid by selecting the user.
But your entitity-relationship-model seems to be fine at all.

How to create a m:n in your case. Open the Relation dialog and add the
tables: tblSessionMembers, tblmembers, tblSession.
Then creat a relationship using drag and drop. Drag from the Primkey and
drop it on the foreignkey.

Regards
Jörn.

Am 04.06.2010 22:49, schrieb jase118:

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?

 

[jase118]

When you setup relations, the fields need the same type: both integer
values. As it seems, your tblSessionMembers is the table to create the
m:n-relationship between members and sessions. Thats why you have to put
your yes/noe-field into this table. If you realise it that way, you can
directly see who has paid by selecting the session, or which session a
user has already paid by selecting the user.
But your entitity-relationship-model seems to be fine at all.

How to create a m:n in your case. Open the Relation dialog and add the
tables: tblSessionMembers, tblmembers, tblSession.
Then creat a relationship using drag and drop. Drag from the Primkey and
drop it on the foreignkey.

Regards
Jörn.

Am 04.06.2010 22:49, schrieb jase118:[quoted text clipped - 76 lines]

I am not sure how to setup a foreign key, went into relationships and setup a
1 to many link, is this correct?

Thanks for the advise so far,
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

 

[Jörn Bosse]

Yep that looks fine. Remember if you need a field in comination with a
user and sessions, you have to put it in that table.
Any more questions?

Jörn


Am 04.06.2010 23:47, schrieb jase118:

When you setup relations, the fields need the same type: both integer
values. As it seems, your tblSessionMembers is the table to create the
m:n-relationship between members and sessions. Thats why you have to put
your yes/noe-field into this table. If you realise it that way, you can
directly see who has paid by selecting the session, or which session a
user has already paid by selecting the user.
But your entitity-relationship-model seems to be fine at all.

How to create a m:n in your case. Open the Relation dialog and add the
tables: tblSessionMembers, tblmembers, tblSession.
Then creat a relationship using drag and drop. Drag from the Primkey and
drop it on the foreignkey.

Regards
Jörn.

Am 04.06.2010 22:49, schrieb jase118:

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 - 76 lines]

I am not sure how to setup a foreign key, went into relationships and setup a
1 to many link, is this correct?

Thanks for the advise so far,
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

 

[jase118 via AccessMonster.com]

Yep that looks fine. Remember if you need a field in comination with a
user and sessions, you have to put it in that table.
Any more questions?

Jörn

Am 04.06.2010 23:47, schrieb jase118:[quoted text clipped - 27 lines]

-logPaid yes/no
-logInstructionsSent yes/no

This is a link to a picture of my tables and relationships, i have attempted
to normalize them and insure the relevant data is in the correct place, let
me know what you think.

http://www.dbforums.com/microsoft-a...d-table-design-layout-newbie.html#post6463536

 

[Jörn Bosse]

Hi,

thats looks fine. But i would add a location to the sessions table and
for the members maybe a date of birth. But the rest is really ok.
You did a gread job so far.

Jörn


Am 05.06.2010 11:13, schrieb jase118 via AccessMonster.com:

Yep that looks fine. Remember if you need a field in comination with a
user and sessions, you have to put it in that table.
Any more questions?

Jörn

Am 04.06.2010 23:47, schrieb jase118:

When you setup relations, the fields need the same type: both integer
values. As it seems, your tblSessionMembers is the table to create the

[quoted text clipped - 27 lines]

-logPaid yes/no
-logInstructionsSent yes/no

This is a link to a picture of my tables and relationships, i have attempted
to normalize them and insure the relevant data is in the correct place, let
me know what you think.

http://www.dbforums.com/microsoft-a...d-table-design-layout-newbie.html#post6463536

 

[jase118 via AccessMonster.com]

Hi,

thats looks fine. But i would add a location to the sessions table and
for the members maybe a date of birth. But the rest is really ok.
You did a gread job so far.

Jörn

Am 05.06.2010 11:13, schrieb jase118 via AccessMonster.com:[quoted text clipped - 14 lines]

http://www.dbforums.com/microsoft-a...d-table-design-layout-newbie.html#post6463536

Thanks, i am moving onto the next step, queries! be ready for many more posts
and thanks again