I'm working on a project in a multi-tenant setting, meaning that most of our tables have an extra column `tenant_id` to denote which tenant the record belongs to. This field is denormalized across multiple records, so a table with a `tenant_id` column could have a foreign-key reference to another table with a `tenant_id`. Obviously we run the risk of getting inconsistent data, but this isn't a major concern. The gains we get by prefixing our indices with `tenant_id` and always filtering by `tenant_id` outweigh any potential negatives.
However, we aren't able to take advantage of the `tenant_id` in our associations. Suppose we use the example of `albums` and `artists` used in the Associations guide, but we add a `tenant_id` column to both of the tables. This `tenant_id` isn't part of any composite key. When fetching the albums associated with a single artist (`artist.albums`) we'd like the generated query to be `SELECT * FROM albums WHERE artist_id = ? AND tenant_id = ?`, where it grabs the `tenant_id` from the artist. I understand that this raises some questions when doing eager loading: What if the original dataset contains records from multiple tenants? I imagine we'd do `WHERE artist_id IN (?, ?, ?) AND organization_id IN (?, ?, ?)`. I would expect Sequel to then wire-up the associations while ignoring the `tenant_id`, and simply avoid addressing the issue of potential data inconsistencies. What would be the best way to get this behavior from Sequel? We also have a few tables that are partially global: Some records have `tenant_id` set to null and are visible to everyone, but other records that do have it set are only visible to that particular tenant. If there's a solution that could also handle this case that would be fantastic. And, related, if there's a higher level solution for multi-tenant settings, that's be great. I'd love to be able to say `Sequel.set_tenant(id)` at the beginning of a request and then have every query to a table with `tenant_id` automatically have a `where(tenant_id: id)` added. If I wanted to implement that sort of solution, what level of the Sequel stack would I want to hook into? Would the `dataset` method associated with each `Model` be the best candidate? Thanks! -- You received this message because you are subscribed to the Google Groups "sequel-talk" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/sequel-talk. For more options, visit https://groups.google.com/d/optout.
