I have three tables: years, employees, positions. Suppose that I already have these data in those tables.
years:
----------------
| id | name |
----------------
| 1 | 2011 |
----------------
positions:
------------------------------
| id | name | year_id |
------------------------------
| 1 | Director | 1 |
| 2 | Manager | 1 |
------------------------------
employees:
---------------------------------------------------------
| id | name | position_id | year_id |
---------------------------------------------------------
| 1 | Employee A (Director) | 1 | 1 |
| 2 | Employee B (Manager) | 2 | 1 |
---------------------------------------------------------
========================================
The years table is a central point. If I insert a new year record, I must also copy all positions and employees which are related to the previous year.
So if I insert year 2012 into the years table, the data is suppose to be like this:
years:
----------------
| id | name |
----------------
| 1 | 2011 |
| 2 | 2012 |
----------------
positions:
------------------------------
| id | name | year_id |
------------------------------
| 1 | Director | 1 |
| 2 | Manager | 1 |
| 3 | Director | 2 |
| 4 | Manager | 2 |
------------------------------
employees:
---------------------------------------------------------
| id | name | position_id | year_id |
---------------------------------------------------------
| 1 | Employee A (Director) | 1 | 1 |
| 2 | Employee B (Manager) | 2 | 1 |
| 3 | Employee A (Director) | 3 (?) | 2 |
| 4 | Employee B (Manager) | 4 (?) | 2 |
---------------------------------------------------------
NOTICE the question marks in the third and fourth row of employees table.
I use these queries to insert a new year and copy all related positions and employees:
// Insert new year record
INSERT INTO years (name) VALUES (2012);
// Get last inserted year ID
$inserted_year_id = .......... // skipped
// Copy positions
INSERT INTO positions (name, year_id) SELECT name, $inserted_year_id AS last_year_id FROM positions WHERE year_id = 1;
// Copy employees
INSERT INTO employees (name, position_id, year_id) SELECT name, position_id, $inserted_year_id AS last_year_id FROM employees WHERE year_id = 1;
The problem is at copying employees query. I can't find a method to get or track the new ID of positions.
Is there a simple method to do this?
Thank you very much.
Your data model is seriously flawed and probably needs a complete overhaul, but if you insist on copying the data like you describe, this should do the trick:
// Copy employees
INSERT INTO employees (name, position_id, year_id)
SELECT name, new_positions.id, $inserted_year_id AS last_year_id
FROM employees
JOIN positions AS old_positions ON old_positions.id = employees.position_id
AND old_positions.year_id = employees.year_id
JOIN positions AS new_positions ON new_positions.name = old_positions.name
AND new_positions.year_id = $inserted_year_id
WHERE employees.year_id = 1
I think you should read about database normalization. Copying the data leads to maintenance issues and erroneous reporting.
If you went with a different design like the following, then there would be nothing to insert, until an employee changes position, is terminated, or a position is discontinued. There are plenty of other ways to approach this, too, but you should minimize redundancy (i.e. have only one copy of each Employee), and then keep track of data that changes over time, separately. Also read about foreign keys before you try to implement something like this.
positions:
-- If you are keeping track of the years that each position is active,
-- using dates provides simplicity. Note: this design assumes that positions
-- are never reactivated after being deactivated.
------------------------------------------------
| id | name | DateActive | DateInactive |
------------------------------------------------
| 1 | Director | 01/01/2011 | |
| 2 | Manager | 01/01/2011 | |
------------------------------------------------
employees:
---------------------------------------------------------------
| id | name | DateHired | DateTerminated |
---------------------------------------------------------------
| 1 | Employee A | 01/01/2011 | |
| 2 | Employee B | 01/01/2011 | |
| 3 | Employee C | 01/01/2011 | 10/01/2012 |
---------------------------------------------------------------
EmployeePositionRelationships
--If you are keeping track of time that each employee held a position
-- Employee A has been a Director since 1/1/2011
-- Employee B was a Manager from 1/1/2011 to 10/6/2012. Then they became a Director
-- Employee B was a Manager from 1/1/2011 to 10/1/2012. Then they were terminated
--------------------------------------------------------
EmployeeId | PositionId | DateStarted | DateEnded |
--------------------------------------------------------
1 | 1 | 01/01/2011 | |
2 | 2 | 01/01/2011 | 10/6/2012 |
3 | 2 | 01/01/2011 | 10/1/2012 |
2 | 1 | 10/6/2012 | |
--------------------------------------------------------