Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Name Value Pair - Part II Expand / Collapse
Author
Message
Posted Monday, May 19, 2008 10:34 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, December 6, 2013 8:07 AM
Points: 109, Visits: 489
You can use EAV or XML for easy maintenance user-defined stuff you don't know is coming in the evolution of application lifecycle - by WHY USE IT FOR THE PARTS OF THE SYSTEM YOU DO KNOW ABOUT?
Post #502954
Posted Monday, May 19, 2008 10:40 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 2:16 AM
Points: 25, Visits: 518
Mark
Mark Horninger (5/19/2008)
Seems like the 'right' answer is to redesign the DB.


No question about it.


Has anyone considered using an xml based approach to replace the E-A-V model?

Not a bad idea if you have got the indexing right.


Post #502959
Posted Monday, May 19, 2008 2:35 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, September 10, 2008 3:17 PM
Points: 10, Visits: 40
I've seen a mixed approach used with some effectiveness. If it's a traditional data structure, with balanced reading and writing, it's hard to mount an effective case to not use the traditional relational table structure. However, to provide some flexibility to your application, mildly customizeable for each different customer, a name-value pair section can be added to provide them a way to save custom data fields. They can then use a 3rd party tool like Crystal Reports to dig into their custom data if your standard interface doesn't do any more than list it.

In the long run though, you should consider making these custom fields part of your app and standard DB design if several customers want it.
Post #503144
Posted Monday, May 19, 2008 2:51 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 24, 2011 7:50 AM
Points: 41, Visits: 83
I have been in similar situations.

Sometimes, for instance, the application is set to support more than one business unit or entity and those units may share a number of data and features, but they may also have a number of fields that are unique to them. If the number of these business units is large, it can be difficult to maintain custom fields through a DB structure for each of them.

In one particular scenario I was in, we provided a number of fields in our DB that we kept in the core data table, which we used meta data to describe based on the business unit. This required less joins and complexity on the DB, but involved more complexity in the application logic.

The XML suggestion seems like a good one if it is available (SQL 2000 folks out of luck I guess), of course if we are talking about lots of fields, then that could get some serious bloat.

It would be nice if sql server allowed for indexes using specific xpath queries, or better yet, if you could store the info using a format like JSON instead. Of course, for enterprising companies, it might be worth considering the creation of some CLR functions for storing and parsing data in that, or similar format.

Post #503152
Posted Monday, May 19, 2008 3:45 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 26, 2014 4:50 AM
Points: 7, Visits: 97
Mark Horninger (5/19/2008)
Seems like the 'right' answer is to redesign the DB.

Having inherited designs like this most likely the reason for the design is to provided the flexibility to add new attributes without re-coding.

Has anyone considered using an xml based approach to replace the E-A-V model?


Designs similar to this are common in applications that allow customizable metadata on stored objects.

I have indeed seen the XML tried for this - all looks very neat (and the developer gets to play with nice new XML features in modern DBs...) until you actually try and query anything. If you thought the performance would be bad with the type of queries you need against this sort of design, think an order-of-magnitude worse for XML. By all means try it for your app, but profile it early and convince yourself on the performance before you waste several weeks on the approach...
Post #503198
Posted Tuesday, May 20, 2008 2:43 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, July 17, 2014 10:36 PM
Points: 5,303, Visits: 1,378
A good article. :)


Post #503379
Posted Tuesday, May 20, 2008 4:25 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 3:07 AM
Points: 1,060, Visits: 876
Cade Roux (5/19/2008)
I love this:
I've learned that when business people tell me that getting to market is more important than running their business well, I'm only there to postpone the inevitable business failure. And when they tell me that they are the business people and I need to let them do what they are good at - they don't even deserve that life preserver.


But then again when they listen to you and sit developing enterprisey applications for the next 3 years, implementing massive infrastructure and spending stupid amount of money to satisfy your search for perfection prior to go live, the market opportunity has passed and you are redundant.

Instead they push it through and rely on their highly paid (or not) technical staff to fix it or work around the issues before it becomes a business killer.




Post #503439
Posted Tuesday, May 20, 2008 6:28 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, December 6, 2013 8:07 AM
Points: 109, Visits: 489
RichardB (5/20/2008)
Cade Roux (5/19/2008)
I love this:
I've learned that when business people tell me that getting to market is more important than running their business well, I'm only there to postpone the inevitable business failure. And when they tell me that they are the business people and I need to let them do what they are good at - they don't even deserve that life preserver.


But then again when they listen to you and sit developing enterprisey applications for the next 3 years, implementing massive infrastructure and spending stupid amount of money to satisfy your search for perfection prior to go live, the market opportunity has passed and you are redundant.

Instead they push it through and rely on their highly paid (or not) technical staff to fix it or work around the issues before it becomes a business killer.



Actually, the original suggested EAV design here is the enterprisey, over-built one and is typical of architecture astronauts.

A good business person recognizes when a project is a failure, cancels and starts over.
Post #503562
Posted Tuesday, May 20, 2008 11:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 19, 2010 7:34 AM
Points: 6, Visits: 192
A good business person recognizes when a project is a failure, cancels and starts over.


A better business person recognizes that starting over is simply courting failure.

The "let's start over" is worse than the "let's accept the current issues and try to improve what we can" method. Instead of continual improvement, even perhaps to a flawed system, you're going to have a time-lag until the new system is in place, and this lag can be fatal to the business.

If we were talking about code, instead of databases, my advise would be to refactor instead of rewrite. When it comes to databases it's a little trickier, there are still options, one of which might be to just accept that the schema has flaws and live with them.


Post #503902
Posted Tuesday, May 20, 2008 11:47 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, December 6, 2013 8:07 AM
Points: 109, Visits: 489
According to the article, the system is not in production. It shouldn't have been allowed to even get this far.

If a system is working, needs tweaks and performs acceptably (even if it takes a minute to do what a properly written system can do in a second), sure patch it up until you have time for a re-write. No system is perfect. Or use views, indexes, whatever you have to do to work around a bad model.

In this case, you aren't in production and you have a major design flaw - you've got to bite the bullet.

You will pay either way - just like with testing - every system is tested, whether by you or by users - either way you WILL pay. I'm not a fan of big up front design or waterfall, but all those people's studies show that defects grow by order of magnitude in cost to correct as you go from design to construction to testing.

It's not just the business' fault - sound like some "architect" went off on some tempting over-abstracted EAV model and never came back down to earth and no one called BS on it. But when the business ignores a truthsayer at an important late stage, it could be a sign of general blindness and self-deception in the business. In fact, it could have helped contribute to no one speaking up earlier. Identifying these things early through open communication is important for technical people to make the business aware of what it takes to meet requirements.
Post #503919
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse