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 >> > >