What are the better ways with INSERT on relational tables?

Question

I'm experiencing my first mysql INSERT tests and I'm using these tables:

table employees
-> employee_id
-> employee_name
-> employee_surname
-> employee_url

table areas
-> area_id
-> area_city
-> area_address
-> area_country

table agencies
-> agency_id
-> agency_name
-> agency_url

table node_employees
-> node_id
-> employee_id
-> area_id
-> agency_id

I would store data in table_employee, table_areas and table_agency but I'm not forced to save all the data simultaneously, so I could create an employee, and subsequently an agency or an address.

In a case of singular data insert, should I use something like this or shoud I use directly the table node_employees, if yes, how can I do it?

INSERT INTO employees (employee_name, employee_surname, employee_url)
VALUES ('Roger', 'Waters', 'http://pinkfloyd.com')

INSERT INTO agencies (agency_name, agency_url)
VALUES ('Google', 'http://google.com')

INSERT INTO areas (area_city, area_address, area_country)
VALUES ('Rome', 'Via Roma, 123', 'Italy')

To link rows each other I've created node_employees, a relational table. I use it to link an employee with an area or an agency, so what I should do to link data with this relational table?

SELECT employee_id FROM employees WHERE employee_name = 'Roger'
SELECT agency_id FROM agencies WHERE agency_name = 'Google'

// I'll get their ids in php
$php_employee_id
$php_agency_id

// and then
INSERT INTO node_employees (employee_id, agency_id)
VALUES ('$php_employee_id', '$php_agency_id')

I have also another doubt, what I should do if I need to link an employee with an area? shoud I use a different query, so a query for every possibility?

// so, not this
$php_employee_id = 12;
$php_agency_id = 7;
$php_area_id = null;
INSERT INTO node_employees (employee_id, agency_id, area_id)
VALUES ('$php_employee_id', '$php_agency_id', '$php_area_id') // will this remove the previous data with null in area_id?

Answer 1

To link rows each other I've created node_employees, a relational table. I use it to link an employee with an area or an agency, so what I should do to link data with this relational table?

That should be two tables - one for relating employees to agencies, and a separate one for relating employees to areas. You haven't mentioned anything relating agencies to areas, giving the impression they are independent of one another...

Regardless, the node_employees.employee_id should be a foreign key to EMPLOYEES.employee_id, in order to ensure that the employee must already exist as a valid value in the system. Likewise for the agency_id between node_employees and agencies tables.

Because of those relationships, values have to exist in the EMPLOYEES and AGENCIES tables before they exist in the NODE_EMPLOYEES table. That makes the NODE_EMPLOYEES table in a "child" relationship with the other two tables, so your three INSERT statements would have to insert into the parents before the child, and use the values from the parents in the child.

Answer 2

I am assuming the agencies and areas are one to many relationships. in other words each employee can be assigned to multiple agencies and areas. If not then I would simply add the agency_id and area_id fields to the employees table and not even create the node_employees table.

With the above in mind...

From the description you have provided it appears agencies and areas are relativally static. So they should be set up before the employee data is entered.

Then when inserting the employee use mysql_insert_id() to return the id of the record you just created. (I am assuming the employee_id is an auto increment field)

Then do as you have said

SELECT agency_id FROM agencies WHERE agency_name = 'Google'

// I'll get their ids in php
$php_employee_id <== from mysql_insert_id() after inserting employee record
$php_agency_id <== from above agency Select

// and then
INSERT INTO node_employees (employee_id, agency_id)
VALUES ('$php_employee_id', '$php_agency_id')

for your last query

An INSERT statement adds a new record it will not replace the existing record. if the existing record has a unique index such as 'employee_id' it will fail with an error. You need to use UPDATE to do that OR the REPLACE statement which UPDATEs a record if one exists or INSERTs if the record doesnt exist. make sure you read up on it because it works via unique indexes.

DC