Re: Database designpattern - product feature

Hello Adrian,

Sorry for the late response. I've not used Django so I can't provide
specific guidance on how you would implement it with that but in SQLAlchemy
that model is called "joined-table-inheritance":
http://docs.sqlalchemy.org/en/latest/orm/inheritance.html#joined-table-inheritance

That is also the behavior of Hibernate (Java) when using "table per
subclass" mapping.

*Will J. Dunn*
*willjdunn.com <http://willjdunn.com>*

On Wed, Jun 3, 2015 at 2:50 AM, Adrian Stern <adrian(dot)stern(at)unchained(dot)ch>
wrote:

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