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

From Adrian Stern
Subject Re: Database designpattern - product feature
Date
Msg-id CAOHZ5L4WyBDQHARceo2Vg6tESMMJonEuci1ruf6H_J+jS_ejrQ@mail.gmail.com
Whole thread Raw
In response to Re: Database designpattern - product feature  (Jan de Visser <jan@de-visser.net>)
List pgsql-general
(Sorry Jan for receiving this twice)

@ Roxanne & Jan:
Thank you both for your Input. I absolutely see your point against EAV. 

I will work out two variants based on the specific solution, where i would create a (bigger) Table for each group of similar products, and based on the dynamic JSON solution. 
With this we can have a good discussion about the pros and cons in our next meeting. In the end, the customer hast to decide how important the flexibility is for him. (And yes, the customer has IT-People who will understand why and what we talk about)

So thanks for doubting my approach, it has been helpful!

Freundliche Grüsse

Adrian Stern
unchained - web solutions

+41 79 292 83 47

On Thu, Jun 4, 2015 at 4:02 AM, Jan de Visser <jan@de-visser.net> wrote:
On June 3, 2015 02:04:28 PM Roxanne Reid-Bennett wrote:
> I think you should evaluate your unease with having to update the database
> on release (potentially many times) carefully for what it is and why you
> have it.  [I'm not saying it is invalid - just know why you have it]
> Because no matter how well you design your system - databases evolve.
> Manage that.

Having been guilty of designing an EAV system before, I think I know his
hesitation is due to the fact that some databases (*cough* Oracle *cough*)
don't allow DDL in transactions (or at least back when I worked with Oracle it
didn't), making dynamic table creation a bit of a problem, especially when you
run in a container which gives you little flexibility in your tx handling
(i.e. you get it all the time, or never). This used to be a problem in many
iterations of J2EE containers. Also, lots of DBAs get skittish when they hear
about applications doing DDL. And again, many of the technological roadblocks
are fixed by now, but the hesitation remains.

Combine this with the fact that you want users to be able to create new
products, which should be built up out of existing and/or newly defined
attributes, you quickly end up with something EAV like. Because you don't want
your product management people coming crying to your DBAs to have a new table
for a new product defined. You want the product management people to point-
and-click their way through a nice GUI.

So this is what lead me to that EAV design for this exact problem: we started
off with Oracle as the database and a temperamental J2EE container, found out
we couldn't do DDL (or at least DDL was hard), and by the time we were on less
brain dead containers and databases the "damage" was done and there was no
going back.

But in my defense I will say that mine was one of the prettiest EAV systems
ever built. In my opinion at least :-)


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

pgsql-general by date:

Previous
From: Jan de Visser
Date:
Subject: Re: Database designpattern - product feature
Next
From: Noah Misch
Date:
Subject: Re: 9.4.1 -> 9.4.2 problem: could not access status of transaction 1