A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

3rd tier level table



 
 
Thread Tools Display Modes
  #1  
Old December 6th, 2006, 03:36 PM posted to microsoft.public.access.tablesdbdesign
Engels
external usenet poster
 
Posts: 45
Default 3rd tier level table

I have a training "events" style database and am adding a new table to record
the different presenters for each event

I already have a "Training course" table, an "Everyone" table and a
"Registrations" table that is related to both tables theough their primary
keys Everyone ID & Training Course ID. I now want to add a "Presenters" table
that will record the multiple speakers/presenters for each course.

I will relate it to the Registrations table through Reg ID but should i also
have fields Everyone ID & Training Course ID on my new table and relate them
directly back to the orignal tables?
How will it affect the creation of forms & queries etc in the future.
  #2  
Old December 6th, 2006, 04:06 PM posted to microsoft.public.access.tablesdbdesign
Roger Carlson
external usenet poster
 
Posts: 222
Default 3rd tier level table

You should only relate your new table to the other tables if ONLY if a
real-world relationship exists.

First question: Does the Presenter REALLY belong in the Registration table?
It's possible, I suppose, but the Presenter doesn't usually have anything to
do with the Registration. That's between the person and the course.

Second: You most likely want to relate the Presenter table to the Training
Course table instead. However, unless each presenter can present to one and
ONLY one Course, you've really got a Many-to-Many relationship. In which
case, you need another linking table (Course_Presenter) which will have the
foreign keys PresenterID and TrainingCourseID.

Thirdly: It looks as though there is only one session of each Course. If
that's the case, then your design is fine, but if there are multiple
sessions of each course, you'll either have to repeat a lot of course
information in your table (bad idea) or create a Sessions table where each
record represents a single session of a Course.

On my website (www.rogersaccesslibrary.com), are several small Access
databases samples that might be helpful. "TrainingRegistration.mdb" is a
working application for registering students for training courses. On my
Tutorials page: http://www.rogersaccesslibrary.com/TutorialsDesign.html
there is a Design Tutorial called "Consolidated Widgets" which is a design
for a "Widget" trade show that has multiple sessions and multiple
presenters. Lastly, there is a sample called
"ImplementingM2MRelationship.mdb" which might give you some help with your
forms question.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L



"Engels" wrote in message
...
I have a training "events" style database and am adding a new table to

record
the different presenters for each event

I already have a "Training course" table, an "Everyone" table and a
"Registrations" table that is related to both tables theough their primary
keys Everyone ID & Training Course ID. I now want to add a "Presenters"

table
that will record the multiple speakers/presenters for each course.

I will relate it to the Registrations table through Reg ID but should i

also
have fields Everyone ID & Training Course ID on my new table and relate

them
directly back to the orignal tables?
How will it affect the creation of forms & queries etc in the future.



  #3  
Old December 6th, 2006, 05:18 PM posted to microsoft.public.access.tablesdbdesign
Engels
external usenet poster
 
Posts: 45
Default 3rd tier level table

Right that was really helpful Roger thankyou but can you clarify something
for me...

but i'll go through the answere to your questions first..

First question: Does the Presenter REALLY belong in the Registration table?


No you are right, it was perfectly possible to do it with the presenter in
the same table but i have decided your way makes more sense.

Second: You most likely want to relate the Presenter table to the Training
Course table instead. However, unless each presenter can present to one and
ONLY one Course, you've really got a Many-to-Many relationship. In which
case, you need another linking table (Course_Presenter) which will have the
foreign keys PresenterID and TrainingCourseID.


I need a Many to Many relationship.

Thirdly: It looks as though there is only one session of each Course. If
that's the case, then your design is fine, but if there are multiple
sessions of each course, you'll either have to repeat a lot of course
information in your table (bad idea) or create a Sessions table where each
record represents a single session of a Course.


Right here's where i am getting confused. I will need to create a Sessions
table as i will have mutiple presenters for multple courses each with
multiple sessions with different presenters presenting more than one session
but maybe even more than one presenter in each session.

Now i am confused. how does that affect the tables & relationships i need to
create.

  #4  
Old December 6th, 2006, 06:41 PM posted to microsoft.public.access.tablesdbdesign
Roger Carlson
external usenet poster
 
Posts: 222
Default 3rd tier level table

In cases like these, it's helpful to write out your relationships in
sentences:

Each Course can have One or More Sessions
Each Session can be for One and Only one Course
(This is a 1:M relationship. You'll put the CourseID in the Sessions table
as a Foreign Key.)

Each Person can take One or More Courses
(BUT WAIT! This is not correct. People don't take Courses, they take a
Session of a Courses. Let's rethink.)

Each Person can take One or More Sessions
Each Session can contain One or More People.
(There. That's right now. Since this is a M:M relationship, you need a
linking table. The Registration table will work for this.)

Each Session can have One or More Presenters
Each Presenter can present at One or More Session
(Again, another M:M relationship, which needs a linking table)

Therefore, your tables would look something like this:

Everyone (Person)
=======
EveryoneID (pk)
(other fields)

Registration
========
RegID (pk)
EveryoneID (fk to Everyone)
SessionID (fk to Session)
(other fields)

Session
======
SessionID (pk)
CourseID (fk to Course)
(other fields)

Course
=====
CourseID (pk)
(other fields)

Session/Presenter
===========
SessionID (fk to Session)
PresenterID (fk to Presenter)
(other fields)

Presenter
======
PresenterID (pk)
(other fields)

As I said earlier, you might find my "ImplementingM2MRelationship.mdb"
sample helpful in implementing these relationship on a form.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L



"Engels" wrote in message
...
Right that was really helpful Roger thankyou but can you clarify something
for me...

but i'll go through the answere to your questions first..

First question: Does the Presenter REALLY belong in the Registration

table?

No you are right, it was perfectly possible to do it with the presenter in
the same table but i have decided your way makes more sense.

Second: You most likely want to relate the Presenter table to the

Training
Course table instead. However, unless each presenter can present to one

and
ONLY one Course, you've really got a Many-to-Many relationship. In

which
case, you need another linking table (Course_Presenter) which will have

the
foreign keys PresenterID and TrainingCourseID.


I need a Many to Many relationship.

Thirdly: It looks as though there is only one session of each Course.

If
that's the case, then your design is fine, but if there are multiple
sessions of each course, you'll either have to repeat a lot of course
information in your table (bad idea) or create a Sessions table where

each
record represents a single session of a Course.


Right here's where i am getting confused. I will need to create a Sessions
table as i will have mutiple presenters for multple courses each with
multiple sessions with different presenters presenting more than one

session
but maybe even more than one presenter in each session.

Now i am confused. how does that affect the tables & relationships i need

to
create.



  #5  
Old December 7th, 2006, 11:40 AM posted to microsoft.public.access.tablesdbdesign
Engels
external usenet poster
 
Posts: 45
Default 3rd tier level table

I've had to adapt what you have said because although i can attach presenters
to individual sessions i can't do the same for the trainees as which sessions
they will take is unlikely to be known in all circumstances and there isn't
really a requirement to differenciate. In most circumstances the trainee will
take all sessions, where they don't i have not got a requirement to record
this information.

If i were starting this from scratch i'd do it your way, unfortunately as i
am now implementing a version 2 of an existing database i have to work within
certain restraints (time being one) and that requires me to keep the
registrations related to the course ID and everyone ID. So this is my
compromise.

Everyone
======
EveryoneID(pk)
(other fields)

Course
=====
CourseID
(other fields)

Registrations
========
RegID
EveryoneID
CourseID
(other fields)

Presenter
======
PresenterID
Everyone ID
(other fields)

Course/Presenter
======
Presenter ID
Course ID

Sessions
=======
Sessions ID
Course ID
(other fields)

Sessions/Presenter
===========
Sessions ID
Presenters ID

Does this make sense. I was considering that i could have a
registrations/sessions link table to implement the option of attaching
trainees to courses without disturbing the course registrations relationship.
It would be nice to have choice to implement this functionality later on even
though i am constrained now.

ps your website is great, simply designed but full of loads of useful
information (databases) i miss those days when you didn't have to squint at
the page to find what you wanted or get bamboseled by flash everywhere.

thanks
  #6  
Old December 7th, 2006, 07:14 PM posted to microsoft.public.access.tablesdbdesign
Roger Carlson
external usenet poster
 
Posts: 222
Default 3rd tier level table

Part of the problem with table design is that it depends a great deal on the
actual business rules. These can be different for different organizations.
So you have to take any design suggested and compare that to YOUR model.

That said, I think that while not optimal, your design could work. I would
also suggest you create the Registration_Session linking table to create a
M:M relationship between Registration and Sessions.

One last thing, however, is you DON'T need the Course/Presenter table.
Since you're assigning Presenters to Sessions and any individual Session can
be for only one class, you can see which presenters are assigned to each
courses that way.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L


"Engels" wrote in message
...
I've had to adapt what you have said because although i can attach

presenters
to individual sessions i can't do the same for the trainees as which

sessions
they will take is unlikely to be known in all circumstances and there

isn't
really a requirement to differenciate. In most circumstances the trainee

will
take all sessions, where they don't i have not got a requirement to record
this information.

If i were starting this from scratch i'd do it your way, unfortunately as

i
am now implementing a version 2 of an existing database i have to work

within
certain restraints (time being one) and that requires me to keep the
registrations related to the course ID and everyone ID. So this is my
compromise.

Everyone
======
EveryoneID(pk)
(other fields)

Course
=====
CourseID
(other fields)

Registrations
========
RegID
EveryoneID
CourseID
(other fields)

Presenter
======
PresenterID
Everyone ID
(other fields)

Course/Presenter
======
Presenter ID
Course ID

Sessions
=======
Sessions ID
Course ID
(other fields)

Sessions/Presenter
===========
Sessions ID
Presenters ID

Does this make sense. I was considering that i could have a
registrations/sessions link table to implement the option of attaching
trainees to courses without disturbing the course registrations

relationship.
It would be nice to have choice to implement this functionality later on

even
though i am constrained now.

ps your website is great, simply designed but full of loads of useful
information (databases) i miss those days when you didn't have to squint

at
the page to find what you wanted or get bamboseled by flash everywhere.

thanks



  #7  
Old December 8th, 2006, 11:04 AM posted to microsoft.public.access.tablesdbdesign
Engels
external usenet poster
 
Posts: 45
Default 3rd tier level table

That's great thank you Roger
I've been stuggling witht the idea of starting the Version 2 of this
database from scratch which i would love to do but would probably mean that
they would pull the plug on the project creating far too much hassle.

"Roger Carlson" wrote:

Part of the problem with table design is that it depends a great deal on the
actual business rules. These can be different for different organizations.
So you have to take any design suggested and compare that to YOUR model.

That said, I think that while not optimal, your design could work. I would
also suggest you create the Registration_Session linking table to create a
M:M relationship between Registration and Sessions.

One last thing, however, is you DON'T need the Course/Presenter table.
Since you're assigning Presenters to Sessions and any individual Session can
be for only one class, you can see which presenters are assigned to each
courses that way.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L


"Engels" wrote in message
...
I've had to adapt what you have said because although i can attach

presenters
to individual sessions i can't do the same for the trainees as which

sessions
they will take is unlikely to be known in all circumstances and there

isn't
really a requirement to differenciate. In most circumstances the trainee

will
take all sessions, where they don't i have not got a requirement to record
this information.

If i were starting this from scratch i'd do it your way, unfortunately as

i
am now implementing a version 2 of an existing database i have to work

within
certain restraints (time being one) and that requires me to keep the
registrations related to the course ID and everyone ID. So this is my
compromise.

Everyone
======
EveryoneID(pk)
(other fields)

Course
=====
CourseID
(other fields)

Registrations
========
RegID
EveryoneID
CourseID
(other fields)

Presenter
======
PresenterID
Everyone ID
(other fields)

Course/Presenter
======
Presenter ID
Course ID

Sessions
=======
Sessions ID
Course ID
(other fields)

Sessions/Presenter
===========
Sessions ID
Presenters ID

Does this make sense. I was considering that i could have a
registrations/sessions link table to implement the option of attaching
trainees to courses without disturbing the course registrations

relationship.
It would be nice to have choice to implement this functionality later on

even
though i am constrained now.

ps your website is great, simply designed but full of loads of useful
information (databases) i miss those days when you didn't have to squint

at
the page to find what you wanted or get bamboseled by flash everywhere.

thanks




 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:57 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.