Here is an example from my database.
I am storing students in one table, and addresses in another. My application allows you to have multiple addresses per student. My application allows you create school bus routes where you could be transporting students to various locations depending on the day of the week, morning vs. afternoon etc.
My Student table has StudentId, Name, etc...
My Address table has AddressId, AddressLine1, City, state, etc...
I have a StudentAddress table that has StudentId, AddressId, Description.
Typical values would be....
StudentId AddressId Description
--------- --------- -----------
101 202 Home
101 203 Mail
101 204 Grandma
101 205 Babysitter
303 404 Home
505 606 Home and mail
You can set up foreign keys here, and you can even have it cascade delete. So... if you delete a student, it automatically deletes the rows from StudentAddress. The logic being, why have student address data for a student that has been deleted.
I don't cascade delete for the address because I don't want users to delete addresses that may be used for a different student. For example, if a sibling graduates and you delete her, but her brother is still in school using the same address, you wouldn't want to delete the address because it is still being used.
And how do inserts work? If I insert into one of the 'real' tables, does it add a record in the 'junction' table?
You don't normally automatically insert in to the junction table.
Using my example from above, if a new house is built and a child moves in from outside the district, I collect the data from the user (front end), then I insert a row in to the student table followed by inserting a row in to the address table, and then finally insert a row in to the student address table.
Junction tables are usually such that you don't need to have rows in them. You can also have 1 row (per main table entry) or multiple rows per main table entry.
Going back to my example above, if the child is homeless, there wouldn't be any rows in the StudentAddress table for them.
Does this help?
Microsoft SQL Server MVP
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom