From: | Dorian Hoxha <dorian(dot)hoxha(at)gmail(dot)com> |
---|---|
To: | Adrian Stern <adrian(dot)stern(at)unchained(dot)ch> |
Cc: | PostgreSql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Database designpattern - product feature |
Date: | 2015-06-01 18:29:38 |
Message-ID: | CANsFX06SOiJ5udZQzXy-LxwyEGeZ6yzWEJz+B--dP=6EK_3JEQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Mimiko | 2015-06-01 19:04:55 | odbc to emulate mysql for end programs |
Previous Message | Robert Haas | 2015-06-01 18:22:32 | Re: Re: [GENERAL] 9.4.1 -> 9.4.2 problem: could not access status of transaction 1 |