3

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.

halfer
  • 19,824
  • 17
  • 99
  • 186
oakhill78
  • 81
  • 1
  • 7
  • This is called a [junction table](http://en.wikipedia.org/wiki/Junction_table). You populate it when, for example, a Student adds a new Address to their profile. You would add a record to the `Addresses` table and then to tie it to a particular student you would write a related record out to the `Students_Addresses` table. – Benny Hill Sep 17 '13 at 13:06
  • You may find searching for "many to many table" also helpful. – halfer Sep 17 '13 at 13:08

4 Answers4

2

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

Haver
  • 443
  • 2
  • 11
  • So, just so I'm clear, I would need to code a separate insert statement to insert the userID and the addressId into the user_address table after the initial data insert in the user and address tables? – oakhill78 Sep 17 '13 at 13:39
  • yes that correct. I don't know any other solution except using ORM. – Haver Sep 17 '13 at 13:44
1

You can save the data in the third table using triggers when the data is inserted/updated/deleted in your base tables. You can learn more about triggers at

mySQL Triggers

However in your case it would be better if you could write the logic at the application/code level to make an entry in the third table. You can set up foreign key relationships to this table from your base tables so that the data remains consistent.

asarfraz
  • 518
  • 3
  • 8
  • I'm not sure how triggers help here, can you clarify in your answer? To add an address to a student, the OP just needs to write to two tables, right? Once to `address` and once for `student_address`. – halfer Sep 17 '13 at 13:10
  • I just wanted to share the information for informational purposes to the questioner so he can see that there is more than one way to save the data to another table. – asarfraz Sep 17 '13 at 13:16
  • Not sure it would work though. If the many:many is to be auto-inserted by the trigger, it wouldn't know which student to join the new address to (unless I am missing something). – halfer Sep 17 '13 at 13:19
1

There is no native method in MySQL to populate Student_Addresses in your situation - you have to take care of entering data (connections) by yourself, but you can use - for example - transactions - see answers in this topic: SQL Server: Is it possible to insert into two tables at the same time?

For taking care of connections consistency - in Student_Addresses make not-null fields for relations to ID from Student and ID from Address, make both of these field as unique key together and use ON UPDATE CASCADE and ON DELETE CASCADE. This will take care of removing records from junction table when removing records from any of two other tables and also won't allow you to add same address to the same student twice.

Community
  • 1
  • 1
Michał Prajsnar
  • 662
  • 3
  • 8
0

I don't think data will be populated automatically rather it's responsibility of user to insert data.

I am note sure about PHP but using Hibernate and Java this can be done seemlessly. Since data of Students and addresses could be coming through some web application Hibernate can map java objects to records in table and also populate relationship table.

halfer
  • 19,824
  • 17
  • 99
  • 186
anonymous
  • 1,920
  • 2
  • 20
  • 30
  • Hi there, you don't need to sign each of your posts - your signature block does that automatically for you. They tend to get deleted here, for conciseness. – halfer Sep 17 '13 at 13:12