From: | Adrian Stern <adrian(dot)stern(at)unchained(dot)ch> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Database designpattern - product feature |
Date: | 2015-06-01 14:35:12 |
Message-ID: | CAOHZ5L6HaAxrmyyXJggrpV1_qOC3XPGA2YecKF8C_oA4jXqZww@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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 | Filipe Pina | 2015-06-01 14:41:47 | Re: pl/python composite type array as input parameter |
Previous Message | Tom Lane | 2015-06-01 14:15:55 | Re: Find out what on what function depends an index |