I’m making a database for safety training and come across something I can’t quite figure out how to do considering best practice.
I have a table like so:
courses
My question is I have several locations where the required field may or may not pertain to.
So should I setup the table like so:
courses
I have about 15ish locations.
Not sure how to go about this.
I do have a separate table for the list of locations.
Alright, I think I understand now, so I should always start with one first.
One employee can have one job title. - Wrong
One job title can have many employees. - Right (Therefore the many table is the employee table)
Is this correct?
One employee can have one job title. - so jobs is the ‘1’ table.
One job title can have many employees. - so employees is the ‘many’ table.
Got it, thanks for explaining it for me.
The first ‘look’ is the correct one
I’m going to add onto this thread since I don’t want to make a new thread for a (probably) silly question.
Question is: How do I determine which table is the many table in a many to one or one to many relationship?
Eg.
Each employee can only hold one job title but each job title can be held by several employees (such as a receptionist position).
However, if we look at it this way:
Several employees can hold a job title but only one job title can be assigned to each employee.
I think I’m just getting my logic messed up a bit from over thinking this.
I would think the job title table would be the many table?
Sorry, I’m new to this but eager to learn.
Actually, I just thought of another way to do this.
I could make a new table called course_required like so:
course_required
Is this a better way of doing this?
Correct, the required field would typically be a checkbox for yes/no.
I’m still trying to figure out how to link that properly to the course table though.
rguy84
September 27, 2010, 5:36pm
10
so you have say 5 different courses, and 15 locations (I assume you mean like buildings on a campus, or different towns). Not each town needs every course…
A table like what you have in post 2 would be best. I would have req_id, course_id, and branch_id. I assume your required field meant yes it is required?
Oh, I understand now. Perfect, thank you.
You don’t need a required field in the course_required table, unless you also want to store non required courses in that table
Edit: what Ryan says
rguy84
September 27, 2010, 6:09pm
14
Ryan_Mortier:
course_required
and the select statement might look like:
SELECT name FROM courses
INNER JOIN course_required ON course.id = course_id
INNER JOIN locations ON location_id = locations.id
WHERE required = yes;
Am I doing this right?
Read my post again. Course_required holds IDs of a course and location where that course is REQUIRED in that location, so all the rows would say yes cause Yes course 101 is required at location a, that is why it would be in a table listing the location with the required courses or in diffferent words all the courses with the required locations listed…
rguy84
September 27, 2010, 5:48pm
15
Stop and think. The course-required table holds the courses REQUIRED by a location, wouldn’t a field asking if course is required be redundant?
edit: ^^ beaten
So I actually don’t need required in the courses table.
It would link like so:
courses
locations
course_required
and the select statement might look like:
SELECT name FROM courses
INNER JOIN course_required ON course.id = course_id
INNER JOIN locations ON location_id = locations.id
WHERE required = yes;
Am I doing this right?
You take one from a table, and see how many there can be in the other one.
