How can I get ORM to work with two separate databases without the foriegn keys defined in the tables?

Question

I'm not even sure that this is possible. I am using the Kohana framework (version 2.3). I have two separate databases. One is called "employees" and the other is called "tracker". Databases are used on two different sites. I want to exclude a table in the tracker database with the name "csr" that contains identical employee data, and associate the tracker with information about employees in the employee database.

In my tracker app, I have a model setup for employees that references an external employee database. I can request it with ORM from the tracker application, and all is well. unique key for employees is "id".

In my tracker database, I have a model for the 'records' table containing about 12 thousand records. None of the field names match the field names in the employee table from the employee database, but some fields contain identical information. Unique key for "records" is Transaction_Number

Please note: I did not write this application or did not design the database. I am trying to “retro-fit” the tracker application to use now centralized employee data.

There are 9 fields in the “records” that contain relevant information in the employee database. These 9 fields contain identifiers and employee names, but not all identifiers are the same.

I can change the data in these 9 fields so that they are the entire identifier of the employee, if that helps, but I need to be able to receive data about employees: names, addresses, etc. based on identifier in any of these 9 fields

Redesigning the database will overwrite the tracker application, and I really don't have time for that.

To save some reading, I do not include table structures here, but I can add them if necessary.

What can I do to link these two tables together?

EDIT: added table structure for tracker.records

TRACKER.RECORDS
Transaction_Number (PK AI not null)
date
accountnumber
reasoncode
reasondesc
actioncode
actiondesc
comments
supervisor        -    employee->id (supervisor that created the record)
supername         -    employee->name
supersuper        -    employee->parent->name
superman          -    employee->parent->parent->name
eid               -    employee->id  (employee that the record is about)
Service_Rep       -    employee->name
ServRepSupervisor -    employee->parent->name
ServRepManager    -    employee->parent->parent->name
csrfollow         -    employee->name  (who to follow up with)
Important
Read
Followup_Read
followup_Important

The employee table uses ORM_Tree as an independent relational table.

. , , . (employee- > id), eid (employee- > id) csrfollow ( employee- > id). employee- > id. , 3 employee.id .

Answer 1

, MySQL , MySQL?

CREATE TABLE `employees` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY
  -- other columns...
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `records` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `employee_id` bigint(20) unsigned DEFAULT NULL,
  -- other columns...
  FOREIGN KEY (`employee_id`) REFERENCES `employees`.`employees` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

.

---

: RECORDS RECORDS_BASE , TRACKER.

TRACKER.RECORDS_BASE
Transaction_Number (PK AI not null)
date
accountnumber
reasoncode
reasondesc
actioncode
actiondesc
comments
supervisor_id
eid
Important
Read
Followup_Read
followup_Important

VIEW RECORDS, RECORDS_BASE EMPLOYEES:

CREATE VIEW TRACKER.RECORDS AS
SELECT rb.*,
  s.id AS supervisor,
  s.name AS supername,
  ss.name AS supersuper,
  sss.name AS superman,
  emp.name AS Service_Rep,
  srs.name AS ServRepSupervisor,
  srm.name AS ServRepManager,
  ??? AS csrfollow
FROM TRACKER.RECORDS_BASE AS rb
JOIN EMPLOYEES.EMPLOYEES AS s ON rb.supervisor_id = s.id
JOIN EMPLOYEES.EMPLOYEES AS ss ON s.parent_id = ss.id
JOIN EMPLOYEES.EMPLOYEES AS sss ON ss.parent_id = sss.id
JOIN EMPLOYEES.EMPLOYEES AS emp ON rb.eid = emp.id
JOIN EMPLOYEES.EMPLOYEES AS srs ON emp.parent_id = srs.id
JOIN EMPLOYEES.EMPLOYEES AS srm ON srs.parent_id = srm.id;

, csrfollow. ? , , . , , .