Hello everyone and thanks for your answers, >> Simpler solutions would be just using hstore or JSON types, but I would be >> loosing the goodies of SQLAlchemy / Postgres schemas and consistency. > > this is totally how I'd want to do it unless your clients are given > access to program in SQL and SQLAlchemy. What is a real-world > scenario where you have given a tenant three additional columns on one > of the database tables and the tenants need to use that data ? what > would that look like and what would you be doing that is any different > from pulling those values from an hstore ?
@Mike: To answer you the user-defined data would only be used for CRUD operations, no specific coding logic involved, so the only benefit of going to using extra fields in a table rather than a hstore / json type would be data and access consistency —probably not enough for the problems you mention this approach would take. As I understand then it is better to just define a Postgres JSON type as the custom field and provide our own schema validation. For the client-defined inheritance, as those tables are going to lack custom logic more than the field definitions, we will value other approaches not touching table definitions. > On 29 Mar 2019, at 00:32, Ibrahima Gaye <ibrahima.g...@gmail.com> wrote: > > Hi Jonathan, > i would do it like this: > - add in your global model tables named attributs, attributs_value and > values, > - any table (let's call it XTable) that will eventually has need extra column > per client will be linked to attributs_value via a table XTable_Attributs > (For maximum flexibility). > Hope that helps, > Best regards @Ibrahima and @Jonathan, as I understand you are talking about something like the Entity–Attribute–Value model <https://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80%93value_model>, adapted to the multi-tenant case. In my case the data comes form an API in JSON, so although there is not a special strong case towards this pattern, I think I will be using a JSON type. Thank you again for your comments; it has been very enlightening! > > > Ibrahima GAYE > > > > > Le jeu. 28 mars 2019 à 21:14, Jonathan Vanasco <jvana...@gmail.com > <mailto:jvana...@gmail.com>> a écrit : > > > On Thursday, March 28, 2019 at 9:19:51 AM UTC-4, Mike Bayer wrote: > > > Simpler solutions would be just using hstore or JSON types, but I would be > > loosing the goodies of SQLAlchemy / Postgres schemas and consistency. > > this is totally how I'd want to do it unless your clients are given access to > program in SQL and SQLAlchemy. > > wile I would handle this as JSON data too, there is also a database pattern > for doing this in multi tenant applications where you use a table to allocate > and store the allowable keys for each tenant , and another table to store the > key values for the tenants objects. but i would do this in JSON. > > > > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ <http://www.sqlalchemy.org/> > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve > <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+unsubscr...@googlegroups.com > <mailto:sqlalchemy+unsubscr...@googlegroups.com>. > To post to this group, send email to sqlalchemy@googlegroups.com > <mailto:sqlalchemy@googlegroups.com>. > Visit this group at https://groups.google.com/group/sqlalchemy > <https://groups.google.com/group/sqlalchemy>. > For more options, visit https://groups.google.com/d/optout > <https://groups.google.com/d/optout>. > > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ <http://www.sqlalchemy.org/> > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve > <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+unsubscr...@googlegroups.com > <mailto:sqlalchemy+unsubscr...@googlegroups.com>. > To post to this group, send email to sqlalchemy@googlegroups.com > <mailto:sqlalchemy@googlegroups.com>. > Visit this group at https://groups.google.com/group/sqlalchemy > <https://groups.google.com/group/sqlalchemy>. > For more options, visit https://groups.google.com/d/optout > <https://groups.google.com/d/optout>. Best regards, Xavier Bustamante Talavera. Linkedin <https://www.linkedin.com/in/bustawin/> | +34 634 541 887 -- 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+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
