Multi-Tenancy with dynamic tables and relationships

[John Kida]

I have a multi-tenant (postgresql schema) system which supports the ability for creating custom tables and relationships. However.... each schema actually consists of the exact same tables.

User can dynamically define new tables and relationships by saving information into TableMeta, FieldMeta and RelationshipMeta tables. The data saved in these tables define the Table mappings i need to dynamically generate.

The relationships that can be dynamically generated can be associated with any of the common tables as well as any of the dynamically generated Tables.

Since relationships can be dynamically added onto common tables on a per schema basis, I would need to generate distinct table mappings per schema, in order to keep those relationships from leaking or colliding with other users and their dynamically added relationships

I read a few posts that duplicating common Table mappings is not recommended because it will not be scalable.

Do you have any suggestions? 

 

 

[mike bayer]

the thread at
https://groups.google.com/forum/#!topic/sqlalchemy/eEPLcy15dGA a few
days ago covers this use case. Basically I recommend a single mapping
and dynamic alteration of the SQL at execution time. If your
multi-tenant setup is separate schema per user, then you can use the
built in multi-tenancy feature:
http://docs.sqlalchemy.org/en/latest/changelog/migration_11.html#multi-tenancy-schema-translation-for-table-objects

> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google
> Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to sqlalchemy+...@googlegroups.com
> <mailto:sqlalchemy+...@googlegroups.com>.
> To post to this group, send email to sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.