AdoDB Gurus: Relate me!
February 11, 2010 8:46 AM   Subscribe

ADODB Gurus: Many to many relation, how can I simulate this using AdoDB for PHP?

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?
posted by gaby to Computers & Internet (5 answers total)
By using an intermediary table, you are creating a many-to-many relationship between users and schools.

Why does it seem wrong to include school_id in the users table? I would think it's a very natural place for the data; the single school with which a user is affiliated would seem to be an attribute of the user.

that's not what the users table is for

Other than storing data about users, what is the users table for?
posted by ElDiabloConQueso at 9:30 AM on February 11, 2010

After replying and then re-reading your question, I'm confused.

You said:

Many to many relation

And then said:

- A school can have many employees
- An employee can only work at one school

Those two statements are contradictory. If an employee can only work at one school, then there should be a one-to-many relationship between school and user, not a many-to-many relationship.

That is, of course, unless you are keeping a historical record of which schools a user has been affiliated with in the past in addition to the current affiliation.
posted by ElDiabloConQueso at 9:36 AM on February 11, 2010

Most of the ORM implementations I've seen make you use the intermediary table as-is and you can write custom methods on top of that if you want to make shortcuts for your N:N relations.

However Cheese Devil is right that the school a user is associated with is user data so the school id belongs in the user table as a foreign key column unless you keep a list of past associations. But in that case, you would probably still want a school id in the user table to signify the current association, and ALSO maintain a table that simultaneously forms your N:N relation and includes extra data like start and end dates, reason for termination, resignation or transfer, etc. You can also handle employees that have no current association with ANY school simply by nullifying the school id column for that user in the user table.
posted by rocketpup at 9:41 AM on February 11, 2010

After replying and then re-reading your question, I'm confused.
You said:
Many to many relation

Sorry, I should have made myself clearer. The users table is for a list of all logins to the system, of where there are currently three types, admin, teacher and one other. The users table is for storing the user login data, username, password, email address and so forth, the basic user account information.

The users are split into their types by having user permissions, stored in the permissions table and linked by the permissions_users table (permission_id, user_id). If a user has the "school" permission then they are a school contact.

The intermediary table between schools and users is used to plug one school into many contacts but without using the users table, I suppose it is simulating a many to many without actually being one. I'm just hesitant to put something like the school id into the users table, it seems to be breaking the purposes of what each table is for.

We are clarifying with the client what the situation is, there's a possibility that one school contact could be working for several schools so it will end up being a m2m relation. This is leaning towards writing a small custom method for the school class so it can read a list of users from the link table.

I'm on a horse.
posted by gaby at 10:11 AM on February 11, 2010

The users table is for a list of all logins

Would it make things better to rename the users table to "logins", and rename the intermediary table to "users"?
posted by hattifattener at 11:22 AM on February 11, 2010

« Older Where would I find a box for a Rolex in Toronto?   |   Should I take this job from the Fund for the... Newer »
This thread is closed to new comments.