Populating a third table to maintain efficiency

Question

I am currently working on a PHP/MySQL project for an assignment. In studying the efficient design of databases while working on the assignment I notice that in many cases it is good practice to create a third table when working with only two sets of data.

For example, if we have a table for "Students" and a table for "Addresses" it appears to be a good idea to create a third table i.e. "Student_Addresses" since a student can hypothetically have more than one address (separated parents etc.) and a single address can represent more than one student (siblings).

My question is: How do we go about populating that third table? Is there a way that it is done automatically using primary and/or foreign keys?

I've tried Google and my textbook to understand this but I've gotten nowhere. Links to tutorials or articles would be greatly appreciated.

Thanks for your help. I hope the question and example are clear.

Answer 1

n:m or 1:m normalization rule

Option 1:

user table
id
f_name
s_name
......

user address table
id
user_id    // this should be index only as foreign keys will allow 1:1 only
address line 1
address line 2
address line 3
address_type (home, office ....)

Option 2:

user table
id
f_name
s_name
......

address table
id
address line 1
address line 2
address line 3
address_type (home, office ....)

user_address table
userId
addressId

according to your description option 2 would be the right solution. After adding the data to user table and address table then you need to add the data to user_address table manually. Some Object relational mapper (ORM) may do add the data to the third table automatically but you need to define the relations. check http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/association-mapping.html.

http://docstore.mik.ua/orelly/linux/sql/ch02_02.htm

http://www.keithjbrown.co.uk/vworks/mysql/mysql_p7.php