UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> One Table Or Several?, Access 2013 Web App    
 
   
tickerrtalk
post Aug 23 2019, 09:39 PM
Post#1



Posts: 4
Joined: 23-August 19



I have recently started learning access, and am running into some questions. I am making a database to track students, instructors, and the associated courses.

My question:
I have four main courses, One, Two, Three, and Four.
Each course is available at four different levels: One-A, One-B, One-C, and One-d, for example.
Each course, with its level, is available as an original course, and as a recertification course.

I am wondering how to design my tblCourse. Do I need a different table for each course type?

Thanks to any answer!
Anne.
Go to the top of the page
 
theDBguy
post Aug 23 2019, 09:48 PM
Post#2


Access Wiki and Forums Moderator
Posts: 76,300
Joined: 19-June 07
From: SunnySandyEggo


Hi Anne. Welcome to UA! welcome2UA.gif

You need a table for courses and a separate table for the levels. You then need a junction table to link up the courses with their levels.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
GroverParkGeorge
post Aug 24 2019, 08:24 AM
Post#3


UA Admin
Posts: 35,668
Joined: 20-June 02
From: Newcastle, WA


Expanding on what theDBGuy said.

Access is a tool for creating Relational Database Applications.

Among other things, that means you must start with a Relational Table Design, following sound principles. Those principles are called the Rules of Normalization. They explicitly define the most effective, reliable way to create tables. They're not "rules" in the sense that they prohibit certain things, or require certain things. They are rules in the sense that they lay out the proper design principles for Relational Database Applications.

One of those principles is, as theDBGuy said, that we have one table for each kind of thing we track. We refer to these as Entities in Relational Database Design.

A Course is an entity. We have one Course table. It contains information, or data, about all of the Courses.

Another entity of importance to you is Students. That means you have a table of Students which contains information about all students (and nothing else).

To match multiple students with multiple classes, you need a third table. This kind of table is needed for situations like yours.

I strongly suggest you invest time in learning more about Relational Database Design BEFORE you start creating tables. Here are some good starter articles.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
dale.fye
post Aug 24 2019, 09:28 AM
Post#4



Posts: 160
Joined: 28-March 18
From: Virginia


Welcome to the world of database development; I hope you grow to enjoy this topic as much as I have over the last 25 years!

I disagree with George (GPC), in that he jumped from Courses to Classes, without actually having a Classes table.

In your case, if the original and re-certification courses are essentially the same, then you might not need a separate course, but I'd assume that the re-certification would have to be a separate "Course" with a separate Course identifier (Basket Weaving 101 vs Basket Weaving 101-R), and would have a slightly different syllabus.

In my mind, Classes are Courses, assigned to specific years, semesters, locations (building-room), and times. Classes would probably also contain a column for instructor (which would require another table Instructors) and maybe other columns. Or might have a related table for ClassLocations if the class meets in more than one location (lecture, lab). In a spreadsheet world, you would be inclined to have a column for ClassBuilding, ClassRoom, LabBuilding, LabRoom; but in a relational database, you would generally take these 4 columns and put them in a separate table ClassLocations with fields ClassID, BuildingID, and Room#. This might also contain information about the Day of Week for each each of those locations and the times, since lectures and labs generally have different start / stop times.

Then, assigning students to classes would require another table (StudentClasses) with fields StudentID and ClassID. Because students are assigned to classes, you can then, via a query, identify information about the student, class (with all of its information about year, semester, building, room, time(s), instructor), and the Course (course description and other characteristics of the Course).

Getting this structure normalized early is important, because getting this wrong means a lot of extra work down the road. This can be challenging, but if you get this right up front, it will make life a lot easier down the road.


--------------------
Dale Fye
Microsoft Access MVP 2013-2016
Developing Solutions, LLC
Go to the top of the page
 
GroverParkGeorge
post Aug 24 2019, 10:03 AM
Post#5


UA Admin
Posts: 35,668
Joined: 20-June 02
From: Newcastle, WA


Dale is right. I oversimplified the situation.

Invest time in learning how to set up a Relational Database Application. And take time to think through all aspects of it.


--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
tickerrtalk
post Aug 26 2019, 10:40 AM
Post#6



Posts: 4
Joined: 23-August 19



Thank you for your reply.
Am continuing to learn, practice, and try out.
I don't want to overload with data until stuff works!

I have:
tblCourses
tblCourseLevels
tblJuncCoursesLevels

Then of course need an event table for when course occurs:
tblCourseEvent

In the course event table, do I include:
CourseEventID
StudentID
CourseID
CourseLevelID
Date
Recertification (yes/no)

Or do I just refer to the junction table...

Thank you for your help.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    16th September 2019 - 05:24 PM