MySQL tables relationship with a foreign key on parent table

[josephbupe]

Hi,

 

I want each new incident created into the t_incidents table by the user to be associated with the AgencyID, a foreign key in the t_users table.

 

The problem is I do not know how this relationship will work and whether I will need a junction table.

 

The two tables are:

t_Users
+----------+----------+------------+
|UserID    | AgencyID |User name   |
+----------+----------+------------+
|1         | 1        |john        |
+----------+----------+------------+
|2         | 1        |andrew      |
+----------+----------+------------+


t_Agencies
+----------+------------+
|AgencyID  |agency name |
+----------+------------+
|1         |police      |
+----------+------------+
|2         |immigration |
+----------+------------+

I will appreciate your advice.

 

Joseph

[Barand]

Put the id in a AgencyID column in the incident table. (as in your data model http://forums.phpfreaks.com/topic/291293-database-tables-relationship/?do=findComment&comment=1492162)

 

You would only need a "junction table" if you have a many-to-many relationship. ie both these statements are true

• An incident is handled by many agencies
• An agency handles many incidents

[josephbupe]

 

Put the id in a AgencyID column in the incident table. (as in your data model http://forums.phpfreaks.com/topic/291293-database-tables-relationship/?do=findComment&comment=1492162)

 

You would only need a "junction table" if you have a many-to-many relationship. ie both these statements are true

• An incident is handled by many agencies
• An agency handles many incidents

 

 

 

Hi Barand,

 

Ok, Thanx.

 

And If understand you well, I will create a foreign key AgencyID in the t_incidents from the t_users table (which also has a foreign key AgencyID from t_agencies).

 

Is that so? Sorry, I just want to be clear on this one, noting also that I slightly departed from my previous table relationship between incidents and agencies after including users table.

 

Joseph

[Barand]

The one you already have should suffice

 

[josephbupe]

Ok, Barand. I will stick to your suggestion.

 

Again thank you.

 

Joseph