I'm loving the ADODB_Active_Record class, really helpful. The problem is that I have a relation that looks like it requires an intermediary table to link the data up. The relation is:
- A school can have many employees
- An employee can only work at one school
- Employees have a user account on the system, as well as administrators, teachers, etc.
I have a users table that holds the user accounts (employees each have a user account), I have a schools table and I have a users_schools table which links user_id to school_id. Using this intermediate table and some plain SQL I can list who works for a given school. Adding a school_id field to the users table seems the wrong approach, that's not what the users table is for so I need the intermediary table to link users to schools.
The issue is that I'm using AdoDB and Active Records so I want to either stick to using plain old SQL or Active Record. There are some one to many functions built into AdoDB
but I can't see one that will let me use the intermediary table directly.
There must be a simple way round this, the problem itself is as old as the moon and must have been solved lots of times. Googling for the problem returns lots of ADO.Net stuff which is not helpful. Can you help an old tech out?