How to pivot a MySQL entity-attribute-value schema

Question

I need to design tables which stores all the (Attributes) metadata of Files i.e., FileName, Author, Title, CreatedDate ... and Custom Metadata ( Metadata added to files by Users)

Before examining the tables we can not say what and how many custom tags have been added on Files. Custom Tags i.i.e, CustUseBy, CustSendBy etc.

Now To store this I have created a Base Table ( Having all Common metadata of Files) Such as :

Table1: FileID, Name, Title, Autor, CreatedDate, i.e.,

and I Attribute Table ( Holing attributes of Files)

Table2: AttributeId, AttributeName, AttributeType. here if the table has 2 Custom Attributes then the Values insert in table is as:

A001, CustomeAttt1, Varchar(40).
A002, CustomUseDate, Datetime.

Table3: Sno, FileId, AttributeId, AttributeValue here Values of custome Attribute is stored with associated FileId. Such as

F001, A001, Akash
F001, A002, 2009/03/02

Now the Problem is I want to show the data in a manner like this:

FileId, Title, Author, CustomAttri1, CustomeAttr2,(All Custom Attribues)
F001    Dox    vinay    Vinay         2009/03/02,
F002    Excel  Ajay

Can Anybody help me to acheive this:

Thanks in advance.

Answer 1

Partial answer since I do not know MySQL (well). In MSSQL I would look at Pivot tables or would create a temporary table in a stored procedure. It may well be a hard time ...

Answer 2

This is the standard "rows to columns" problem in SQL.

It is most easily done outside SQL.

In your application, do the following:

1. Define a simple class to contain the file, the system attributes, and a Collection of user attributes. A list is a good choice for this collection of customer attributes. Let's call this class FileDescription.

2. Execute a simple join between the file and all of the customer attributes for the file.

3. Write a loop to assemble FileDescriptions from the query result.

   • Fetch the first row, create a FileDescription and set the first customer attribute.

   • While there are more rows to fetch:

     • Fetch a row
     • If this row's file name does not match the FileDescription we're building: finish building a FileDescription; append this to a result Collection of File Descriptions; create a fresh, empty FileDescription with the given name and first customer attribute.
     • If this row's file name matches the FileDescription we're building: append another customer attribute to the current FileDescription

Answer 3

The general form of such a query would be

SELECT file.*,
   attr1.value AS 'Attribute 1 Name', 
   attr2.value AS 'Attribute 2 Name', 
   ...
FROM
   file 
   LEFT JOIN attr AS attr1 
      ON(file.FileId=attr1.FileId and attr1.AttributeId=1)
   LEFT JOIN attr AS attr2 
      ON(file.FileId=attr2.FileId and attr2.AttributeId=2)
   ...

So you need to dynamically build your query from the attributes you need. In php-ish pseudocode

$cols="file";
$joins="";

$rows=$db->GetAll("select * from Attributes");
foreach($rows as $idx=>$row)
{
   $alias="attr{$idx}";
   $cols.=", {$alias}.value as '".mysql_escape_string($row['AttributeName'])."'";   
   $joins.="LEFT JOIN attr as {$alias} on ".
       "(file.FileId={$alias}.FileId and ".
       "{$alias}.AttributeId={$row['AttributeId']}) ";
}

 $pivotsql="select $cols from file $joins";

Answer 4

However there are solutions to use lines as columns, aka transpose the data. It involve query tricks to do it in pure SQL, or you will have to rely on certain features only avaible in certain database, using Pivot tables (or Cross tables).

As exemple you can see how to do this here in Oracle (11g).

The programming version will be simplier to maintain and to make and moreover will work with any database.

Answer 5

The question mentions MySQL, and in fact this DBMS has a special function for this kind of problem: GROUP_CONCAT(expr). Take a look in the MySQL reference manual on group-by-functions. The function was added in MySQL version 4.1. You'll be using GROUP BY FileID in the query.

I'm not really sure about how you want the result to look. If you want every attribute listed for every item (even if not set), it will be harder. However, this is my suggestion for how to do it:

SELECT bt.FileID, Title, Author, 
 GROUP_CONCAT(
  CONCAT_WS(':', at.AttributeName, at.AttributeType, avt.AttributeValue) 
  ORDER BY at.AttributeName SEPARATOR ', ') 
FROM BaseTable bt JOIN AttributeValueTable avt ON avt.FileID=bt.FileID 
 JOIN AttributeTable at ON avt.AttributeId=at.AttributeId 
GROUP BY bt.FileID;

This gives you all attributes in the same order, which could be useful. The output will be like the following:

'F001', 'Dox', 'vinay', 'CustomAttr1:varchar(40):Akash, CustomUseDate:Datetime:2009/03/02'

This way you only need one single DB query, and the output is easy to parse. If you want to store the attributes as real Datetime etc. in the DB, you'd need to use dynamic SQL, but I'd stay clear from that and store the values in varchars.