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

[TheShabz]

If this is something that will be done for a business, I suggest you get professional help. The one time fee for proper setup is well worth it if you know enough to maintain.

[jase118]

i agree it would be better to get it done professionally but this is a shoe string out fit and we are currently doing it all on excel spread sheets and things are far from perfect!
I am a sailing instructor not a DB designer but i am going to give this a good go and see if i cant improve a bad situation with a bit of hard graft.

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.

[TheShabz]

the form sounds about right. I'd add a few fields though.
1. in members add a ActiveInd field which basically is a 1/0, yes/no, whateveryouwant field that lets you know if an entry is a current member. Having archived data of previous member is always good and when you query you can set your WHERE clause to query only active accounts.
2. under Courses I'd add an isAvailable field. again a 1/0, yes/no whateveryouwant field that will be updated to signify if a course is currently in use. This will help determine vacancies.

the SessionMembers table is there to accomodate a Many to Many relationship between Session and Members. If you havn't already, read up on realtionships to ensure you understand what the purpose is and which tables to query for specific information.

Also read up on Normalization. I can't stress this part enough. We have way too many questions here that are seemingly unrelated but stem from normalization issues.

take care and good luck.

[jase118]

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?

We dont have members but do have customers who use us alot, im not sure if i would need to specify active or inactive members?

[TheShabz]

Read, Read, Read. It's the best you can do.

Paid Indicator... not sure. Might go under SessionMembers.
The foreign key... correct
ActiveInd.... your call

[jase118]

I have put it under:
tblSessionMembers
-pkSessionMemberID primary key, autonumber
-fkSessionID foreign key to tblSessions
-fkMemberID foreign key to tblMembers
-logPaid yes/no
-logInstructionsSent yes/no


I have a tut on relationships, relational database principles sticky and am reading up on normalization.
Will read these before setting up my relationships or adding anymore fields.

Im glad i didnt just make a quick atempt at this, it seems to be taking the right sort of shape thanks to all your help! not going to be quick or easy though will keep you posted on future developments!

[TheShabz]

If it was quick or easy, most of us would be out of a job =P

[jase118]

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.

since posting this i have changed my phone fields to txt instead of numbers.