Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've been forced to use a very uncomfortable structure for a table. After trying to up similar examples, I've discovered the EAV, which seems almost like a pivot of said structure. The attribute order is not set in stone (except that there are no new attributes after a NULL column). For the max amount of attributes N, there are 2*N columns in pairs of Attribute_Name - Attribute value.

EAV example:

ID| Entity  | Attribute  | Value
__________________________________
1 | Entity1 | Attribute1 | Value1
2 | Entity2 | Attribute1 | Value2
3 | Entity2 | Attribute2 | Value3
4 | Entity2 | Attribute3 | Value4
5 | Entity3 | Attribute1 | Value5
6 | Entity3 | Attribute2 | Value6

Example of my table:

Entity  | A_Name1    | A_Value1 | A_Name2       | A_Value2 | A_Name3       | A_Value3
______________________________________________________________________________________
Entity1 | Attribute1 | Value1   | NULL          | NULL     | NULL          | NULL
Entity2 | Attribute2 | Value3   | Attribute1    | Value2   | Attribute3    | Value4
Entity3 | Attribute2 | Value6   | Attribute1    | Value5   |  NULL         | NULL

Should this be considered separate from EAV structure? Is there any classification for this kind of table structure, or is it one of a kind? Basically, I want to learn as much as possible about it and ways to work around it.

EDIT: Forgot to mention -- the Attribute names might also repeat like this:

Entity  | A_Name1    | A_Value1 | A_Name2       | A_Value2 | A_Name3       | A_Value3
______________________________________________________________________________________
Entity1 | Attribute1 | Value1   | NULL          | NULL     | NULL          | NULL
Entity2 | Attribute2 | Value3   | Attribute1    | Value2   | Attribute2    | Value4
Entity3 | Attribute2 | Value6   | Attribute1    | Value5   |  NULL         | NULL
share|improve this question
    
What are you trying to do? –  Colin 't Hart Feb 19 at 11:47
    
I am trying to find ways to make working with the table data and performing queries more comfortable. –  Worse_Username Feb 19 at 11:53
    
Both of these structure are very bad anti-patterns, see slideshare.net/billkarwin/sql-antipatterns-strike-back where both the EAV and numbered columns anti-patterns are listed. –  Colin 't Hart Feb 19 at 11:53
    
See slides 14 & 15 on avoiding "numbers" in column names, while the coverage on EAV starts at slide 16. –  Colin 't Hart Feb 19 at 11:55
    
EAV looks like heaven when one has to deal with this design :) –  ypercube Feb 19 at 13:30
show 1 more comment

1 Answer 1

The process of pivoting data is often called transposition, as it is similar to the matrix operation of that name in mathematics (http://en.wikipedia.org/wiki/Transpose), though that isn't really what you are doing in your example.

In your output you still have the EAV pattern but stored less efficiently. The columns "Entity, A_Name1, A_Value1" is one attribute in the EAV pattern, "Entity, A_Name2, A_Value2" is another, and so forth. Each of your value columns could contain a range of probably unrelated attribute values so you are not in any sort of normal form and reporting is likely to be even more difficult than the original layout.

A proper transposition in this case would be:

Entity  | Attribute1 | Attribute2 | Attribute2 
----------------------------------------------
Entity1 | Value1     | NULL       | NULL       
Entity2 | Value2     | Value3     | Value4     
Entity3 | Value5     | Value6     | NULL       

This way each column contains data for only one attribute.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.