I have three tables (people, clients_people, clients) that would be joined using
SELECT * FROM people LEFT JOIN clients_people ON people.id = clients_people.person_id LEFT JOIN clients ON clients_people.client_id = client.id
Each client record has a location - L1, L2, and L3 (client.location = 'L1', etc.). I want the ability to store exclude locations for each person; e.g. I would like to define people.id=1 to exclude clients.location = 'L1' and clients.location = 'L3' in a new query.
I have two solutions right now.
- Add column client_location_exclude to the people table and store 'L1,L3' in people.id=1 record.
- Allow for negative integers to be stored in clients_people.client_id that define the exclusions; e.g. clients_people.client_id = -1 defines an L1 exclusion, clients_people.client_id = -2 defines an L2 exclusion, etc.
Are either of these good solutions (pros/cons)? Is there a better (best) solution?
The data is going to be used in a Ruby on Rails application and a PHP (no framework) application; but from my perspective, I'm more interested in how the tables should be structured to hold and easily utilize the exclusion of locations.