Re: Database designpattern - product feature

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