Hello Adrian, Sorry for the late response. I've not used Django so I can't provide specific guidance on how you would implement it with that but in SQLAlchemy that model is called "joined-table-inheritance": http://docs.sqlalchemy.org/en/latest/orm/inheritance.html#joined-table-inheritance
That is also the behavior of Hibernate (Java) when using "table per subclass" mapping. *Will J. Dunn* *willjdunn.com <http://willjdunn.com>* On Wed, Jun 3, 2015 at 2:50 AM, Adrian Stern <adrian.st...@unchained.ch> wrote: > Hi William, thanks for joining the conversation. > > 1) We do hope for constraints since a connection to an ERP system is > possible in the future. We want to plan ahead. > > 2) As for the subclass approach: I would need about 30 subclasses and it > will get really hard to add new products since a change in the database > will be necessary each time. That's why we want a more generic approach. > > Maybe I don't understand you right, because of the language barrier. Can > you provide me a link to a subclassing example? > -> > https://docs.djangoproject.com/en/1.8/topics/db/models/#multi-table-inheritance > ? > > ORM is a given in my case. This is not a high performance application. > > Freundliche Grüsse > > Adrian Stern > unchained - web solutions > > adrian.st...@unchained.ch > +41 79 292 83 47 > > On Tue, Jun 2, 2015 at 5:35 PM, William Dunn <dunn...@gmail.com> wrote: > >> Hello Adrian, >> >> May I ask why you need a non-standard model? By standard models I mean >> the following: >> >> 1) When you don't need to have subclass specific database constraints: >> All subclasses in the same table, subclasses that do not have an attribute >> have that column null. This has the best performance because no joins are >> required when querying both superclass and subclass attributes, and all the >> data for an object will be in the same block on disk. The disadvantage of >> this is that you cannot enforce constraints, such as not-null, on subclass >> specific attributes columns because the constraints would also be applied >> to the superclasses. If you can ensure that your application is the only >> way data can be inserted those constraints will naturally be enforced there. >> >> 2) When you need subclass specific database constraints: Use an ORM such >> as Django's ORM or SQLAlchemy which has one table with the superclass >> attributes and a table for each subclass with their subclass specific >> attributes. This is slower because joins will be needed and the data for an >> object will be in 2 different blocks of disk but it allows you to enforce >> constraints within the database which will be checked whenever any >> application tries to insert values. There is a lot of complexity added >> because there will be so many small tables and indexes but the ORM takes >> care of that for you. >> >> *Will J. Dunn* >> *willjdunn.com <http://willjdunn.com>* >> >> On Mon, Jun 1, 2015 at 10:35 AM, Adrian Stern <adrian.st...@unchained.ch> >> wrote: >> >>> Hi, I'm new >>> >>> I've been working as the sole administrator of various postgresql >>> projects for a while now. All of which where django projects. >>> Since a new project is starting and we've found the need for a more >>> generic approach I would like to ask a few questions. >>> >>> I would like to implement a pattern similar to the product feature >>> pattern explained in the silverstone book - the data model resource book >>> vol 1. It is simply explained. There is a Table PRODUCT holding the fields >>> all the products share, then there is the table PRODUCT_FEATURE, both of >>> them in a “many to many“ relationship. >>> >>> PRODUCT <--- m -------- n ---> PRODUCT_FEATURE (a table in between of >>> course) >>> >>> PRODUCT_FEATURE --> PF >>> PRODUCT --> P >>> TABLE IN BETWEEN --> TIB >>> >>> PF defines the feature Type while P stands for the product the feature >>> is applied to. Some of these PF can have values of different types (text, >>> numbers, floating, blob, ...) which would be applied to TIB. >>> >>> I don't like the idea of having numerous empty fields prepared in TIB, >>> just to store occasional values of different types, therefore I need to >>> specialize those TIB Values. >>> >>> Now how would I do That? >>> >>> I could create some tables solely for the means of holding [NUM], >>> [TEXT], [BLOB], [ETC] and reference them with the TIB PK. When using them I >>> could create a view TIBV containing all of [NUM, TEXT, BLOB, ETC] in the >>> same column called Value, and join it with TIB to get the value of a PF. >>> >>> But is this a good idea? >>> Is there a better way? >>> >>> Also, I would have to create a pivot table in order to list all the >>> products with all the features. As this is implemented in C (afaik) I >>> suppose it is rather fast or at least fast enough, but I do not actually >>> know. What I know is, there are about 30 Product Types and around 50 >>> possible product features. One product can have up to approximately 25 PF >>> but are mostly around 5 to 10. >>> >>> Do you think a pivot table is a good idea? >>> What alternative do i have? >>> >>> There is room for caching since the dataset is not updated too often. >>> >>> regards, adrian >>> >> >> >
