What about keeping all the dynamic columns of each product in a json(b) column ? Maybe you can make constraints that check the product_type and json->field->type ?
On Mon, Jun 1, 2015 at 4:35 PM, 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 >
