Search Postgresql Archives

Database designpattern - product feature

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux