May 13, 2008 at 10:42 am
I don't believe the goal is flawed, only the concept of its implementation. Instead of telling your client simply "it's a bad idea", why not recommend a better solution that meets the desired goal? It would be simple to create a set of tables to contain metadata used to alter the primary data table structure. The flexibility would remain, and whatever solution they go with they will need to design a robust but easy to use front end...
May 13, 2008 at 11:18 am
In rereading the original post, I have some questions. You mentioned that all the data would be
in one large varchar column, of one huge table.
I got the impression that this would be a single-column row in a single-row table.
Then you mention
another table that contains a "Label" and row type id that can be used to find the item in the other table.
OK. So maybe the data is separated into lines by placing actual carriage-return and/or linefeed characters in the data. Is this what you mean by "row"? Or will there be many single-column rows in the first table?
But then you really say something confusing:
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.
You failed to mention these 7-8 other tables and what part they play in all this. It does sort of belie your "put all data in one column of one table" introductory remarks.
Then you admit to having used this method before:
when storing the questions and answers to surveys, where the questions change from one year to the next.
Now I'm really confused. What was stored in one large column, the questions or the answers? How did either achieve any benefit to the condition that questions changed?
I find that possibly I have, along with others, given a knee-jerk answer to a situation that you have failed to make completely clear. In fact, I see that you have made vague statements that lead us to look one way while the real action is happening elsewhere -- a little verbal sleight-of-hand. Perhaps a more detailed explanation would clarify?
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
May 13, 2008 at 4:36 pm
The real fun will start when you do a large batch update that escalates to a table lock. When it finally completes after 4 hours, everyone can go back to work. Provided it doesn't become a deadlock victim and spend the next 4 hours rolling back...
May 13, 2008 at 6:20 pm
To Tomm Carr,
While I am not going into details of the design. I would just say that the other tables I am talking about are used to help query the one large table that contains [highlight=#ffff11]all of the actual data[/highlight]. This is much like SQL Server uses a sysobjects and syscolumns to organize the data that it is storing.
These seven or eight other tables used to help organize the data in the one huge table. These tables organize the data based on which business entity it referrs to and what screen the data is dispalyed on. If you think about querying a database you know nothing about, by first looking at sysobjects to find the tables and then looking at syscolumns to find the columns in each table, then finally using this information to query the actual data, you will get the idea of how this works.
When I say row I am refering to a SQL Server row, not a row in a text file.
When I say that I have used this concept before on a limited basis, I am talking about a specific application that is storing the questions and answers to surveys within a larger traditionally designed data base. The idea was that we do a survey each year. Each year there are a set of questions asked and a set of answers to each question. The web page that displays the questions has to dynamically build itself based on data in the database. The answers are linked back to the question using a "QuestionId" foreign key. All the answers to all the questions are stored in the same column within the same table. We can update this system by just creating a new set of questions and possible answers for each year. We don't have to change the website code, the database stored procedures, or the database structure.
For this narrow case I believe this concept works pretty well.
What I am asking about is extending this idea to store every item of data in the entire database [highlight=#ffff11](I am excluding the meta data that is in the seven or eight other tables), [/highlight]using the same column in the same table.
May 13, 2008 at 6:31 pm
Thank you to erveryone that participated in this poll. Especially to those who left comments (some of them funny :P). I am glad to know that everything I have learned in 25 years of programming is not wrong.
Michael
May 19, 2008 at 4:03 pm
Michael,
If you do get stuck building this, an artilcle was just posted that addresses making the best of a bad situation.
Name Value Pair article on SQLServerCentral.com: http://www.sqlservercentral.com/articles/Database+Design/62804/
May 20, 2008 at 7:19 am
A similar mess:
http://www.simple-talk.com/opinion/opinion-pieces/bad-carma/
I like the quote: RUN LIKE HELL!
May 20, 2008 at 2:17 pm
Just the thought of writing simple queries for that system already gave me a headache. And I feel sorry for the last developer to say "Not it!" when management begins requesting reports...
We've all heard that a million monkeys banging on a million typewriters will eventually reproduce the entire works of Shakespeare. Now, thanks to the Internet, we know this is not true. -- Robert Wilensky
May 21, 2008 at 1:09 am
JohnG (5/20/2008)
A similar mess:http://www.simple-talk.com/opinion/opinion-pieces/bad-carma/
I like the quote: RUN LIKE HELL!
I knew I'd seen this before as a real world fail, now I know where cheers for the reminder 😀
Hiding under a desk from SSIS Implemenation Work :crazy:
May 21, 2008 at 3:34 am
I do favour XML columns to store business objects, but this should only be done when entities are not fixed, like survey or question answers applications etc.
I prefer to have bulk data in nText column but they should supported by primary keys and other column which can help in faster select statements.
May 22, 2008 at 1:25 am
Why not go one step ahead and store the data in a text file (xml) instead of a database.
Text files even support replication out of the box! And you should already have a backup strategy on your fileserver which covers this textual database.
You even have access to powerful reporting capabilities (XSLT) at no extra cost!
If you find any sarcasm in this post, you may keep it.
Edit: Just to add at least some small value to this poll:
The biggest issue with the polled solution is that it is not scalable.
Often people say, "it does not need to scale, it's only for x users"
or similar arguments. But I have seen only rare cases where such
expectations remained true throughout the whole product lifecycle.
Businesses (any kind) are usually growing, therefore at some time
badly designed applications will hugely suffer performance if it was
not designed well from the beginning. As soon as such issues arise,
you probably have one option left -> completely restart from scratch.
Best Regards,
Chris Büttner
September 3, 2009 at 4:43 pm
Update:
The project was a huge failure as I expected.
It was so convoluted that the required functionality was never able to be implimented. Most screens never worked as expected even after three years of development. The performance was horrible, with some screens taking minutes to come up. After millions and millions of $s were spent the project is now "On Hold" indefinitely.
The manager that was in charge of this disaster is now "Currently Pursueing Other Oppertunities".
September 3, 2009 at 5:07 pm
michael.welcome (9/3/2009)
Update:The project was a huge failure as I expected.
Heh... "So say we all." Thanks for the feedback. Most don't remember.
Now that the patient has died, my question would be... if you have the nice 2k3 Corvette paid off, can we know what the project was supposed to do? If not, understood.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 3, 2009 at 5:16 pm
Yes,
The Corvette is paid for now. All is good. I am working on projects that are actually enjoyable again.
September 3, 2009 at 5:39 pm
What was the failed project supposed to accomplish?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 31 through 45 (of 54 total)
You must be logged in to reply to this topic. Login to reply