Thread: table structure for current project.

Basically, I am designing a student planner for a specific major in my university. We are coding in Excel/VBA and using Access as our database. As of now we have two tables "Users" and "Classes". Once a user logs in, we want to show them the specific classes that they have completed along with the classes that they have yet to take. I can't decide the best way to go about doing this without being redundant... Do I need to create a new table for each user, "Last_Name_Classes_Completed" for example, or is there a better way to "add up" all the classes that someone has taken based on the unique "class_id" found in the "Classes" table?

I hope that I was clear in what I am attempting to accomplish, but if you need any more information, let me know.
Edit/Delete Message

Hi jimmycantler,

Please don't build a table for each user!

Consider your two tables "Users" and "Classes" as Parent tables. They also represent separate entities in your database. What you need to build now is a third table, a Child table, between "Users" and "Classes" (call it something like "tblClassUser"?). This table will hold the individual relations between your two entities. It will only really need three columns, a primary key, the key from "Users" (as a foreign key), and the key from "Classes" (also as a foreign key). You'll may want to add course dates/times to this table as well, but that depends on how you've decided to structure the rest of your data.

Good luck with your major!

Cheers,

I appreciate the help! So basically, You could consider the third table "Completed Classes" for example, and would contain a list of classes specific to each user. For example, user_id 19 just completed class_id 20 , so I would create an entry in the new table with class_id 20 and user_id 19, as well as any other relevant information I would want to include (course_description) for example. Do I have the right idea? I appreciate your help as I am still figuring out table relationships and the like.


Thanks!


Edit:

This is a screenshot of my current table setup:
http://imgur.com/2976.jpg


I can't seem to wrap my head around the relationship that I need.

So when a user completes a class, I would add an entry to ClassComplete with the class_id that they completed and the user_id of whoever completed the class?

If that's the case, then what would my SQL statement be that would allow me to show all of the classes that someone had completed. Would I select from the ClassComplete table or the Classes table?

Hi jimmycantler,

Yes, that is the idea, but not just for "completed" classes. You can also include the classes they've yet to take in the same table. That's why I suggested a more generic name like "tblClassUser". All you'd do would be to add fields for start date and end date to the third table. You could also add a field for grade attained, if you wanted to track that.

The whole idea is this third table resolves the issue of there being a many-to-many relationship between Users and Classes.

Note 1 - In your User table you have a field for "completed_classes"... you probably don't want that one there.

Note 2 - In your Classes table you have a field for "user_id"... you REALLY don't want that one there.

Note 3 - You have a "major" field in the User table, a Majors table, and a "Major_id" field in the Classes table. Watch out for building a "ring" with your joins. It will cause you grief.

Cheers,


Notes for your edit:

You be looking at a one-to-many join between Users and tblClassUser, and a one-to-many join between tblClassUser and Classes.

So when a user completes a class, I would add an entry to ClassComplete with the class_id that they completed and the user_id of whoever completed the class?

Yes, exactly!

If that's the case, then what would my SQL statement be that would allow me to show all of the classes that someone had completed. Would I select from the ClassComplete table or the Classes table?

Your SQL would select from all three tables (with the join info), you'd want to show the students name and a list of all their classes.

Cheers,

Thanks so much for all the help, it's all starting to make sense. I'm currently just struggling with the join statement. Here are the relationships that I set up.

http://imgur.com/29cDQ.jpg


Okay right now I have a user with "user_id=20" and he has completed a class with "class_id=2"

I want to display a table with the following:

ClassDescription, ClassAbbreviation from Classes table
Where (user_id=20 in the ClassUser table)

Code:

Select ClassDescription, ClassAbbreviation from Classes ...

I've tried several different types of join statements at this point, but i can't seem to relate the user_id and class_id of the ClassUser table with the class_id, ClassDescription, and ClassAbbr from the Classes table.


Once again, thanks so much for all the help!


Edit: Think I solved it all

Code:

SELECT Classes.ClassAbbr, Classes.ClassDescription, ClassUser.userID
FROM Classes INNER JOIN ClassUser ON Classes.classID = ClassUser.classID
WHERE (((ClassUser.userID)=20));