Re: Database designpattern - product feature

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(dot)stern(at)unchained(dot)ch
+41 79 292 83 47

On Tue, Jun 2, 2015 at 5:35 PM, William Dunn <dunnwjr(at)gmail(dot)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(dot)stern(at)unchained(dot)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
>>
>
>