Help needed with table design/layout for newbie
in [Tables]
|
Prev: Confused over new db
Next: Core Competency Assessment
From: jase118 on 4 Jun 2010 14:20 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
From: jase118 on 4 Jun 2010 15:07 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! >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.
From: jase118 on 4 Jun 2010 16:49 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?
From: Steve on 4 Jun 2010 17:15 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 santus(a)penn.com "jase118" <u60596(a)uwe> wrote in message news:a90a1aff03e1a(a)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? >
From: Jörn Bosse on 4 Jun 2010 17:26
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: > 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? > |