In the previous lesson, we looked at One to One Relationships.
Now let's take a look at the next type of relationship that we can create in our Postgres database.
And this is probably one of the most frequently used relationships,
it's a One to Many relationship.
In a lot of cases, this is the relationship that you're going to be falling back on because it occurs
very frequently in data storage.
The way that a One to Many relationship works is if you think about a case where you have something
that has only one record, say, one student, but that student happens to have many of some other type
of data, then you end up with a One to Many relationship.
In the example here, we have our student's table as we had from before,
so in our school we have our students,
but each student makes many homework submissions,
and this is how we end up with a One to Many relationship.
Each piece of homework can only have one student who did the work.
We don't have group projects in our school, but one student can be associated with many pieces of homework.
That's why if you take the student as a starting point, then you would call this a One to Many relationship.
But if you take the homework submission as the starting point, then you would call this a Many to One
relationship.
Now this is one example,
another example that you can imagine is if you have a customer database, say you sell Christmas trees
and you want to keep track of your customers.
That customer can make many orders, right?
They might make an order for a tree in 2012.
They might make another order for another tree in 2020, and so on and so forth.
So this orders table is a Many.
And this customer table is a One.
And if we want to link up that relationship, if we want to have some sort of representation of this
type of relationship, then that is when we need our Many to One relationship.
Notice in our database design, the homework table has a crow's feet representation linked to the student
id field.
So this again is the Foreign Key in this table and this in the student table,
the id is the Primary Key. In our crow's feet representation,
the Many side has this three-legged sort of like a bird-feet kind of representation,
and then on the One side, we again have that same type of representation.
So this is One to Many.
Another thing to note here is that in the Homework Submission, each Homework Submission has its own
Primary Key because there are many pieces of homework that is submitted,
so each of these records has to be uniquely identified from each other.
The Foreign Key, however, is the field that links it back to the student that created it.
So this field stores the id of the student who made that piece of homework.
Let's see this for real by creating this table called homework_submission.
This is the SQL command that is required to create this table.
It's called homework_submission and it has just three fields.
It has a PRIMARY KEY field right here that uniquely identifies each record in the homework_submission.
So each homework might have a different mark,
it might have a different teacher,
it might have a different class,
it might have a different date,
but for simplicity's sake I've only added three fields.
What is the mark that the student got, and what is the id of that piece of homework?
And what is the student's id
who created that piece of homework?
This is how we establish a Many to One relationship.
One student has many homework submissions.
And again we use that REFERENCES keyword to set up our Foreign Key.
Inside this table, there is something, an id that identifies a foreign table,
hence it's the Foreign Key.
And so this entire line is how we set up our Many to One relationship.
We give the field a name.
We say that it has to be a whole number, because this id is also a whole number
and they basically have to map to each other.
We use the REFERENCES keyword to set up this as the Foreign Key and set up the relationship.
And then here we target the student table and the field in that table which is called id.
This part is probably the most interesting,
we're saying look for the student table,
find the field or the column in there that's called id, which is of course this one right here,
and then set this as a relation to this field here.
Let's go ahead and create this table for ourselves in pgAdmin so that we can work with it.
Again I'm going to go into the querie.sql,
I'm going to copy this command that creates the homework_submission table,
I'm going to paste it into my queries under the school database,
and now I have created my tables.
And if I hit Refresh I now have a new table called homework_submission.
Now, just be careful when you're working with databases in terms of plural or singular.
There's a lot of debate around this.
You could call this table contact_details, homework_submissions, students,
you could have it pluralized, basically.
But you'll also see other people name everything in singular such as contact_detail, student,
it really is actually up to you or the company that you work at, where they define these rules,
but if the rules are in place, try to keep it the same.
So don't have a table that's called contact_details and a table called student that doesn't really work.
So it doesn't really matter if you name your tables pluralized or singular, but just stick to one particular
scheme.
Now that we've created our homework_submission table, let's add some data into that table.
So copy this part of the SQL query.
And what this code is going to do is it's going to insert some values into our homework_submission table,
and it's just going to add the mark and student id.
So remember this table also has an id field which is set as SERIAL,
so it will automatically generate it starting from 1, 2, 3 and increment automatically for each new record.
The only part that we need to add-in is the mark and the student id.
The mark is the grade or the score that the student got for that piece of homework, and the id corresponds
to the student id in the student table.
So I know I've added one record in there,
so the first record has an id of 1 and that's me.
I think it's 1,
and then it's Angela Yu,
and essentially what we're saying is we have three pieces of homework right here, three new records
and they have different scores, but they're all associated with actually the same student.
Once we go ahead and run this code, we will now have some data in our homework submissions table,
and if we take a look at it, you'll see that we have the id's of each of these pieces of homework,
the id of the student who created these homeworks, and the mark that they got for each piece of homework
submission.
This is a little bit confusing to look at as a human, because I don't know which student this id corresponds
to.
So let's utilize this relationship that we've created the Many to One relationship, and let's write
some SQL code that will generate this table that is all joined up.
Notice how again it is two tables,
first, this is the student table,
and it is joined to the homework_submission table.
And the criteria that is used for this join is that this student id has to match this id in the student
table.
And remember this is the One and this is the Many. If you remember the SQL command from the previous
part,
so I recommend don't look inside the queries.sql in VS Code just yet because the answer is right
there.
Have a think about how you might write the code based on what you did previously, with the One to One relationship,
where you had to use the JOIN ON command in order to join these two tables together.
Try it out, maybe fail, but at least give it a go and have a think about it before you join me and
we go through the solution together.
All right.
So what we want to do is we want to firstly SELECT, right? And SELECT allows us to define what we want
to select.
We're going to say everything.
And then we get to say, well, which table do you want to select FROM?
And in this case, you can choose student or you can choose homework_submission,
it doesn't really matter.
Next what we want to do is we want to define the JOIN ON criteria.
This is going to be the criteria for our JOIN,
and this is going to be the table that we want to join to this student table.
The code looks like this.
SELECT * everything from the student table,
JOIN it to the homework_submission table based on this criteria,
the student.id,
so this is the student table,
the id field which is this one, must be equal to the student id in the homework_submission table.
In this case, because we're saying JOIN to homework_submission,
then we can, in this same line, define it just as student id, but if you want it to be more clear,
you could also write homework_submission.student_id just as what we've done here, table.field.
If you run this command as it is in pgAdmin, you will end up with this join table that's created.
We found the records and we've lined up the records so that the student id matches the id in the student
table.
And in this case, even though the names occur many times, they represent every record in the homework_submission
table.
As you can see, in this case, it's a little bit extra.
We've got the id, the id, the student id, all of these things show up so many times.
What if we only wanted certain parts of this table after it's been joined?
Well, we can narrow down our SELECT.
So, in this case, I'm saying select the student.id, the first_name, the last_name, and the mark, and
we end up with a table that is a little bit more shortened and a little bit more easier to understand.
You can always use this, as you might already know, to select on the particular fields that you want
in the final table.