Thanks Xavier, i did not know the pattern had a name :) ( the Entity–Attribute–Value model <https://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80%93value_model> ,) Ibrahima GAYE
Le ven. 29 mars 2019 à 09:39, Xavier Bustamante Talavera <busta...@gmail.com> a écrit : > 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> 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/ >> >> 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. >> > > -- > 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. > > 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. > -- 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.