May 9, 2008 at 9:40 am
This is not a joke. A company I know of has actually decided to build a large production database where all of the data will be place in one large varchar column, of one huge table. There is another table that contains a "Label" and row type id that can be used to find the item in the other table. They say this will allow them to add new data items to the system without having to update the schema.
Among the issues I see are:
There is no way to index anything.
Querying the data is a nightmare and requires joining 7-8 tables together to get one item that would be a column in a more traditional database.
Because all data is stored in character form, doing any compares will first require that the entire data set you are looking at be converted to the correct type. (What happens when data that is supposed to be a date does not convert without an error?)
Performance will be terrible.
I have used this approach before, for certain limited cases. For example when storing the questions and answers to surveys, where the questions change from one year to the next.
I am just wondering what industry professionals thing about this.
May 9, 2008 at 9:52 am
I assume there will be some sort of rules engine that will determine how many "Name" attributes a particular type of object can have and how to relate the "Name" to the "Age" and "Address" of a single object.
This can be a flexible approach, but taken to extremes like you are suggesting means that adding what would be a column will require making programming changes to the rules engine - or at least application of new rules. This tends to be more complicated than the programming that would have been required around a simple schema change.
These also tend to be the kind of "it will be so flexible we will never have to change it" types of projects that are doomed to failure on several levels. The initial development time is usually huge and having no incremental releases. The development team will end up being the only pool of expertise and the risk of employees leaving becomes a major issue. Inevitably, the extreme flexibility becomes the very thing that makes it rediculously inflexible - everyone becomes paranoid about making a change to the rules engine or the rules themselves because it can such a huge impact that nobody fully understands.
I would have to guess that this idea is being put fourth by an architect that does not have very much real-world experience. During my time I have learned to keep things simple, have incremental deliverables, and don't try to anticipate so far into the future that you have sacrificed tomorrow.
Good luck.
May 9, 2008 at 10:06 am
Interestingly enough - it's kind of a "heretical" version of 6NF, where you essentially end up with many tables each essentially holding a key and a data column. Heretical because they didn't catch the "many tables" part of the converstion. Whatever they're proposing here is ultimately just a mess.
Unless you ARE dealing with a "survey" application, the "renormalizing" process, along with the myriad CAST operations, never mind the utter lack of any data validation, will completely overrun any advantage you might get from "not messing with the schema". Using a VARCHAR(MAX) to store dates is, well - ludicrous, and will cause you much pain.
6NF is very flexible (this is NOT it like mentioned earlier), but not performant at this point. It's a war drum being beaten heavily by some fairly prominent folks as something that can be used to eliminate NULL's. It just doesn't scale worth of a damn for anything production-sized....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 9, 2008 at 10:32 am
This is usually called an EAV (Entity, Attribute, Value) design.
Simply put, this approach is always a disaster. The disadvantages are so overwhelming that the small benefit they think they might get will look ridiculous.
You will have what should be simple queries that are 3000 lines long, run forever, and produce incorrect results.
The data will have huge numbers of errors that are impossible to explain or debug.
You will spend all you time trying to explain to end users why the price of a product is “Green”, the description is $47.02, and the quantity on hand is “411 Elm Street”.
Edit:
Just in case you are wondering, I voted for:
"This is the dumbest thing I have heard of and will never work. The guy who suggested it should be beaten with a large stick. The people who decided to do this should be fired immediately."
May 9, 2008 at 10:37 am
I think having a quantity of "411 Elm Street" indicates some overstock.
I would put them on sale.
May 9, 2008 at 11:02 am
Michael Earl (5/9/2008)
I think having a quantity of "411 Elm Street" indicates some overstock.I would put them on sale.
Stop - that would be a nightmare. And when that happens on Elm Street, people start getting hurt in their sleep, and that crazy guy with the bad sweater shows up, etc....:D
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 9, 2008 at 11:23 am
Michael Valentine Jones (5/9/2008)
Simply put, this approach is always a disaster.
I love it! 😀
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
May 9, 2008 at 11:33 am
Agree with others here. Of course, this is a good way to ensure job security as someone will have to clean this mess in the future
Marvin Dillard
Senior Consultant
Claraview Inc
May 9, 2008 at 11:56 am
No amount of job security is worth that headache.
May 9, 2008 at 3:45 pm
This has to be one of the dumbest ideas that I have ever heard.
How can you index the data and get any decent type of performance.
May 9, 2008 at 4:36 pm
"A company I know of "
Good - I hope that means you are not an employee, so I do not need suggesting updating your resume.
This is going to be implemented on Oracle, yes ? 🙂
Can you tell us the name of the company ? Then when this becomes a disaster, I can bill enormous hours to straighten out the mess.
Seriously, I evaluated one of these EAV applications about 9 years ago and the only solution was to scrap the entire DB and start over.
Here is an alternative solution that makes as much sense:
Create TheTable
(TableId bigint identity(1,1) not null primary key
,Data image null
)
go
SQL = Scarcely Qualifies as a Language
May 9, 2008 at 4:58 pm
There's a couple of chances this might work... "Slim and None"... and Slim just left 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
May 9, 2008 at 5:25 pm
I noticed that one person said "This is brilliant and I wish I though of it". Please post some reasons why. Have you had experience with this? Have you ever seen it actually work in a production environment? What kind or application did it work for? How did you manage data integrity and performance issues? How were you able to produce reports easliy that performed well?
May 9, 2008 at 5:38 pm
Just remember to let us know how this works out. Also, besides, company, I want to know the Director of IT or whomever is over the programming there. I want to ensure that when/if I see that name I unsubmit my resume'.
Although Carl does have a point. Can you imagine the bill rate. I mean, this has to be worth at least a new (insert favorite sports car here) to straighten out after implementation.
Marvin Dillard
Senior Consultant
Claraview Inc
May 9, 2008 at 5:53 pm
Well I could post the company name, embarasing them in a public forum could be grounds for firing, so I will not. I have payments on my 2003 Red Corvette Z06 (Really I do have one) that need to be made. So far we are about 18 months and millions of dollars into this project and nothing works. I figure my Vet will be payed off by the time this project ends.
That is pretty much what I expected, but wondered if anyone else had a different experience with this.
Viewing 15 posts - 1 through 15 (of 54 total)
You must be logged in to reply to this topic. Login to reply