A nudge in the right direction please

amjad171

Registered User.
Local time
Today, 23:53
Joined
Nov 7, 2007
Messages
13
Hello,

I am new to this forum and Access databases in general, me and a colleague have been given the task of recreating our course brochure into an online searchable webpage. The web side of things I am ok with, its just the designing of the database that is causing us both endless problems.

Before I go into more detail about what it is that we need and showing the example Access database we have created so far, can I ask if I am posting this is the right section and secondly can I attach a database to a post?

Many thanks
 
zip up the db and yes this is the right place for DB design as their are no specific places for general design...
 
Thanks RSMONKEY,

Attached is my database. The Database has 5 tables, the ones in question which I would like to know are correct are the "Courses" and "NetworkCourses". The other 3 tables I am happy with as they are fairly straight forward.,

The current information in the database is dummy info, but the real information will be along the same lines.

A network course is a course which spans over a couple of days or months and in between there are "researchFrom" and "researchTo" periods that are relevant for that course. In between each day of the course there can be "researchFrom" and "researchTo" periods, so if a course is 3 days it will have day 1 of the course, then the research periods, then day 2 and the research periods then day 3. This information is stored in the "NetworkCourses" table.

A course is added into the "courses" database and if it is a Network course the others days and research periods of the course are added into the "NetworkCourses" table. I have linked the two together with "CourseID". Me and my colleague think we have done it correctly, however I guess there is no harm in getting a third party with more experience to give their opinion.

I think I have explained it as well as I could but obviously if you need more info, just ask.

Many thanks
 

Attachments

Hi,

Is anyone able to have a look at my database in the previous post to confirm if they think it is ok?

I have to have this completed very shortly and not being experienced in Access is what is bringing this doubt into my head. Any help is greatly appreciated.

Thanks
 
Just a quick look:

1) Day, date and time are all one attribute. You only need one field set as a date/time datatype.
2) RepeatedCourse and NetworkCourses have identical fields to your main table. That cannot be right

You haven't explained the business process very well so it's dificult to comment on your db design.
 
Thanks for that neil.

The reason RepeatedCourse and NetworkCourses have similar information to the main Courses table is because they pretty much hold similar info to the Courses table with the exception of having different courseCode, Day, Date, Time and Venue. For example a RepeatedCourse can be exactly like a course in the Courses table but with the above mentioned values being different, does this need to just be another row in the Courses table or is it better as it is being linked via RepeatedCourse?

The NetworkCourses are difficult to explain but i'll try my best, a Network course is a course that can span over a couple of days or months and the thing that makes it a Network course is the fact that it has a research period in between the course days, so if a course ran for 3 days then in between each day there would be research periods so that would be Day 1 of course then research period, Day 2 then research period then the final Day 3. A network course can be 2, 3 or 4 or possibly more Days long. Is it not better to link that to the courses table or should it be in a differently laid out table?

Any suggestions to improve my design?
 
I think you might benefit from reading this and attempting to understand it before continuing. Your design, based on your description (I have not been able to look at it at this point), strikes me as not normalized.

http://support.microsoft.com/kb/283878
 
Thanks bob,

I did read something similar when designing the DB with my colleague, I know that the RepeatedCourse and NetworkCourses have similar information to the main Courses table and this maybe does not make it normalised, however when we start creating the web pages for this to go on the web, we will be extracting information from the queries tables, these do bring up the information as we want it.

The information will only be added to the database once a year so there is not the constant adding/editing etc

To be fair not being a DB designer and not having anyone else in the workplace to help with it, is the reason why I have turned to experts like yourselves.
 
As you had titled the thread "A Nudge in the right direction" I was giving a little nudge there. But, just quickly (because it's 2AM here in Portland and I need to go to bed so I can get up in about 4 hours), you shouldn't have repeating fields regardless of your final usage. That is what queries are able to do for you is to be able to pull out the data that you want so you shouldn't need to approach it this way, but instead look to the power of a relational database and the tools that are inside it in order to get what you want.

If the others have not solved this with you by the time I get the time to focus on it more, then I will work in greater detail with you to try to get it to where it is designed well and will get you what you want out of it.
 
Thanks Bob,

I better let you get some sleep seen as it's 2am there. The example on the link you sent are good so i'll see what can be done, im at work for another 7 hours yet at it just turned 10am here and im in til 5pm, so we may get to speak later.

good night.
 
I have been looking a bit closer at my tables, I think the Courses table can be split, some fields in the table like courseDay, researchFrom, researchTo are not always populated and reading the normalisation stuff, this is not the way it should be, so I have created some tables with TEST on the end, I have created a new CoursesTEST, researchTEST which has the courseDay, reseacrhFrom and researchTo in it and is linked via a courseID to the CoursesTEST table, and NetworkCourseFinalDayTEST which has the final course day details like courseDay, Date etc this is also linked to the CoursesTEST table.

I have zipped the tables again and attached them if anyone can pass on some useful hints and tips like you have been doing already, thanks.
 

Attachments

Users who are viewing this thread

Back
Top Bottom