Re: Database designpattern - product feature - Mailing list pgsql-general

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

On Mon, Jun 1, 2015 at 10:35 AM, Adrian Stern <adrian.stern@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