SQL for Smarties | SQL Programming Style | Trees and Hierarchies in SQL | SQL Puzzles and Answers | Data and Databases

Monday, June 05, 2006

Changing the columns of a SELECT statement

SQL Apprentice Question
I have been "normalizing" some of the tables that we have been using and I
ran into a problem in one of our stored procedures.

It used to be (with the old table) that you could do something like:


The stored procedure essentially does this.

Now with the new table(s) it is something like:

FROM Table
LEFT OUTER JOIN Attributes ON Attributes.AttributesId = Table.AttributesId

There is in the Attributes table an AttibutesTypeId and an AttributesValue.
So I get 'A' only when AttributesTypeId = 1 and 'B' when AttributesTypeId =
2. The question is how do I return A and B from the stored procedure given
this new table structure? I am tempted to create a temporary table and use a
cursor to move row by row through the table, but there must be a more
efficient way.

Celko Answers
Find the moron that did this to you and kill him. This is called a EAV
design and it is totally wrong in an RDBMS. It confuses data and meta
data and makes your most basic queries run several orders of magnitude
slower. But the lack of speed does not matter; you will have no data
integrity so you cannot trust the answers anyway.

You might also want to get any book on data modeling. Then you will
know that there cannot be such a creature is a "type_id" -- the data
element is either a type with a known code or it is an identifier of
some kind of entity.

You can Google old postings on EAV, OTLT and MUCK as
bad design decisions frequently made by non-SQL programmers.

No comments: