Description of Issue / Question
I want to be able to specify a default or primary relationship of a table to a row in the database that the first table already has a many-to-many relationship with.
Example / Usecase:
A user can be a member of multiple organizations. We do this by creating a many-to-many relationship via an association table between "user" and "organization". I want the user to be able to specify a "primary" organization
What I've tried
organization: - id (integer, pkey) - name (varchar) user - id (integer, pkey) - email (varchar) - primary_org_id (fkey to organization table) <-- "Primary Organization" user_organization_assoc: - organization_id (integer, fkey to organization table) - user_id (integer, fkey to user table) * This also has a unique constraint on (organization_id, user_id)
My initial issue was
DROP dependency issues - this was fixed by properly naming the foreign key constraints. It now "works", but several other theoretical issues arise with this solution.
The most obvious issue is the lack of integrity between the
user_organization_assoc table and
user.primary_org_id. When a user is removed from an organization, we have to remember to manually update the
primary_org_id value of the user table.
- How would you do this?
- Am I going about this the wrong way?
- This is a concept that is seen a lot in SaaS and other applications, are there any resources on this?
This development work was initially being done in python + sqlalchemy, but now it is mainly a question of principles / theory.