Three Column Tables
-
I'm curious as to what people think about a three-column design in a database. We use them extensively where I work and, frankly, I'm not a big fan. The concept is that you have a table that contains three columns: ID, Name and Value. You can then store lots of information in a denormalized fashion.
So, for example, if I wanted to describe a car I could use the type of car as the ID along with information in the other columns. Ferrari | Color | Red; Ferrari | Speed | Fast; Ferrari | Cost | High, etc. etc. It's basically a collection of name/value pairs for a particular entity.
These kinds of tables are a complete pain in the butt to use for obvious reasons. They're difficult and slow to query, they have no strong design which makes them error prone and so on. I guess they're a good way to store lots of data in a generic and extensible fashion, but I just don't think the benefits outweigh the costs.
Honestly, I never experienced this design before coming to this company but the people here act as if they're natural and a great way to do things in a database. Does anyone else use them?
-
The only time I use this is for my flags table > FlagName, FlagValue. This works in my small environement and because this flags are checked by jobs to know if they need to run or not. Other than that it would just be a pain in the ass to run in that context.
-
G'day,
When used properly, these sorts of tables can be very beneficial. Improved performance, simplified joins, simplified maintenance CAN be had. The key phrase is "when used properly". It sounds like someone in your firm took the concept of 4th normal form to an extreme, and then twisted the concept a bit.
I have lost track of the thread, but somewhere on this site is a series of posts about applying fuzzy logic to selecting data. In that thread, tables such as you describe are the most elegant, best performing solution.
Another case where the pattern is reasonable involves a middle tier in the system that caches most if not all of the data in a set of objects. The data structure you describe is reasonably efficient for serializing objects into and out of persistent storage. Personally, I think the serialization arguement is a copout for lazy programmers, but I offer it here in the interest of playing devil's advocate.
Random thoughts
Wayne
-
I don't really see why this design should be slow to query, I guess it depends upon how free form the attributes are - in a form of KB then I'd say this was fine as there may not be a finite list of attributes and from an analytical point of view it would be good. The down side where the attributes are not controlled, as anyone who's had to cope with data cleansing will confirm, is multiple spellings of the same thing. I once encountered 23 different spellings of assistant .
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
We use them only rarely when pushed into a corner. They have all the downsides that you've described with little, if any (in my way of thinking) offsetting benefits. If you want to take that design concept to the extreme, why have multiple tables at all? Why not just one table? Most good database design isn't going to rely heavily on name/value pair tables.
-
23 spellings????????????????
can we see a list
. -
sorry don't have that data any more - moved company! I was cleaning job titles - had the best published in press - some memorable ocupations were Golden Wonder Crisp, Well Rubber, Land Rover, Jaguar.
As Joe points out the typo's etc. make the implementation very poor although the concept might sound good. I wasn't actually recommending this method just attempting to see the other side < grin >
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Thanks to all for the feedback. It confirmed what I thought about these tables. Now if only I could convince people at my company about the problem... =)
-
It isn't possible to use these abominations properly.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
And trying to turn them back into what looks like a normal resultset to feed into a something as simple as a 2D spreadsheet or something like Crystal reports is a nightmare
-
"I wasn't actually recommending this method just attempting to see the other side < grin >"
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply