mysql - relacional - generar diagrama entidad relacion phpmyadmin

Cómo pivotar un esquema de valor de atributo de entidad MySQL

Question

Necesito diseñar tablas que almacenen todos los metadatos de los archivos (es decir, nombre de archivo, autor, título, fecha de creación) y metadatos personalizados (que los usuarios han agregado a los archivos, por ejemplo, CustUseBy, CustSendBy). La cantidad de campos de metadatos personalizados no puede establecerse de antemano. De hecho, la única manera de determinar qué y cuántas etiquetas personalizadas se han agregado a los archivos es examinar lo que existe en las tablas.

Para almacenar esto, he creado una tabla base (que tiene todos los metadatos comunes de archivos), una tabla de Attributes (que contiene atributos adicionales opcionales que pueden establecerse en los archivos) y una tabla FileAttributes (que asigna un valor a un atributo para un archivo )

CREAT TABLE FileBase (
    id VARCHAR(32) PRIMARY KEY,
    name VARCHAR(255) UNIQUE NOT NULL,
    title VARCHAR(255),
    author VARCHAR(255),
    created DATETIME NOT NULL,
) Engine=InnoDB;

CREATE TABLE Attributes (
    id VARCHAR(32) PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    type VARCHAR(255) NOT NULL
) Engine=InnoDB;

CREATE TABLE FileAttributes (
    sNo INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    fileId VARCHAR(32) NOT NULL,
    attributeId VARCHAR(32) NOT NULL,
    attributeValue VARCHAR(255) NOT NULL,
    FOREIGN KEY fileId REFERENCES FileBase (id),
    FOREIGN KEY attributeId REFERENCES Attributes (id)
 ) Engine=InnoDB;

Data de muestra:

INSERT INTO FileBase
(id,      title,  author,  name,        created)
  VALUES
(''F001'', ''Dox'',   ''vinay'', ''story.dox'', ''2009/01/02 15:04:05''),
(''F002'', ''Excel'', ''Ajay'',  ''data.xls'',  ''2009/02/03 01:02:03'');

INSERT INTO Attributes
(id,      name,            type)
  VALUES
(''A001'', ''CustomeAttt1'',  ''Varchar(40)''),
(''A002'', ''CustomUseDate'', ''Datetime'');

INSERT INTO FileAttributes 
(fileId, attributeId, attributeValue)
  VALUES
(''F001'', ''A001'',      ''Akash''),
(''F001'', ''A002'',      ''2009/03/02'');

Ahora el problema es que quiero mostrar los datos de la siguiente manera:

FileId, Title, Author, CustomAttri1, CustomAttr2, ...
F001    Dox    vinay   Akash         2009/03/02   ...
F002    Excel  Ajay     

¿Qué consulta generará este resultado?

Answer 1

Este es el problema estándar de "filas a columnas" en SQL.

Se realiza más fácilmente fuera de SQL.

En su aplicación, haga lo siguiente:

1. Defina una clase simple para contener el archivo, los atributos del sistema y una Colección de atributos de usuario. Una lista es una buena opción para esta colección de atributos del cliente. Llamemos a esta clase FileDescription.

2. Ejecute una unión simple entre el archivo y todos los atributos del cliente para el archivo.

3. Escriba un ciclo para ensamblar FileDescriptions desde el resultado de la consulta.

   • Obtenga la primera fila, cree una FileDescription y establezca el primer atributo de cliente.

   • Si bien hay más filas para recuperar:

     • Obtener una fila
     • Si el nombre de archivo de esta fila no coincide con la FileDescription que estamos construyendo: termine de construir una FileDescription; añada esto a un resultado Colección de descripciones de archivos; crea una FileDescription fresca y vacía con el nombre de pila y el primer atributo de cliente.
     • Si el nombre de archivo de esta fila coincide con la FileDescription que estamos construyendo: añada otro atributo de cliente a la actual FileDescription

Answer 2

He estado experimentando con las diferentes respuestas y la respuesta de Methai fue la más conveniente para mí. Mi proyecto actual, aunque usa Doctrine con MySQL, tiene bastantes tablas sueltas.

El siguiente es el resultado de mi experiencia con la solución de Methai:

crear tabla de entidades

DROP TABLE IF EXISTS entity;
CREATE TABLE entity (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255),
    author VARCHAR(255),
    createdOn DATETIME NOT NULL
) Engine = InnoDB;

crear tabla de atributos

DROP TABLE IF EXISTS attribute;
CREATE TABLE attribute (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    type VARCHAR(255) NOT NULL
) Engine = InnoDB;

crear la tabla attributevalue

DROP TABLE IF EXISTS attributevalue;
CREATE TABLE attributevalue (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    value VARCHAR(255) NOT NULL,
    attribute_id INT UNSIGNED NOT NULL,
    FOREIGN KEY(attribute_id) REFERENCES attribute(id)
 ) Engine = InnoDB;

create entity_attributevalue join table

DROP TABLE IF EXISTS entity_attributevalue;
CREATE TABLE entity_attributevalue (
    entity_id INT UNSIGNED NOT NULL,
    attributevalue_id INT UNSIGNED NOT NULL,
    FOREIGN KEY(entity_id) REFERENCES entity(id),
    FOREIGN KEY(attributevalue_id) REFERENCES attributevalue(id)
) Engine = InnoDB;

poblar la tabla de entidades

INSERT INTO entity
    (title, author, createdOn)
VALUES
    (''TestFile'', ''Joe'', ''2011-01-01''),
    (''LongNovel'', ''Mary'', ''2011-02-01''),
    (''ShortStory'', ''Susan'', ''2011-03-01''),
    (''ProfitLoss'', ''Bill'', ''2011-04-01''),
    (''MonthlyBudget'', ''George'', ''2011-05-01''),
    (''Paper'', ''Jane'', ''2012-04-01''),
    (''Essay'', ''John'', ''2012-03-01''),
    (''Article'', ''Dan'', ''2012-12-01'');

poblar la tabla de atributos

INSERT INTO attribute
    (name, type)
VALUES
    (''ReadOnly'', ''bool''),
    (''FileFormat'', ''text''),
    (''Private'', ''bool''),
    (''LastModified'', ''date'');

poblar tabla de valores de atributos

INSERT INTO attributevalue 
    (value, attribute_id)
VALUES
    (''true'', ''1''),
    (''xls'', ''2''),
    (''false'', ''3''),
    (''2011-10-03'', ''4''),
    (''true'', ''1''),
    (''json'', ''2''),
    (''true'', ''3''),
    (''2011-10-04'', ''4''),
    (''false'', ''1''),
    (''ascii'', ''2''),
    (''false'', ''3''),
    (''2011-10-01'', ''4''),
    (''false'', ''1''),
    (''text'', ''2''),
    (''true'', ''3''),
    (''2011-10-02'', ''4''),
    (''false'', ''1''),
    (''binary'', ''2''),
    (''false'', ''3''),
    (''2011-10-20'', ''4''),
    (''doc'', ''2''),
    (''false'', ''3''),
    (''2011-10-20'', ''4''),
    (''rtf'', ''2''),
    (''2011-10-20'', ''4'');

poblar la tabla entity_attributevalue

INSERT INTO entity_attributevalue 
    (entity_id, attributevalue_id)
VALUES
    (''1'', ''1''),
    (''1'', ''2''),
    (''1'', ''3''),
    (''1'', ''4''),
    (''2'', ''5''),
    (''2'', ''6''),
    (''2'', ''7''),
    (''2'', ''8''),
    (''3'', ''9''),
    (''3'', ''10''),
    (''3'', ''11''),
    (''3'', ''12''),
    (''4'', ''13''),
    (''4'', ''14''),
    (''4'', ''15''),
    (''4'', ''16''),
    (''5'', ''17''),
    (''5'', ''18''),
    (''5'', ''19''),
    (''5'', ''20''),
    (''6'', ''21''),
    (''6'', ''22''),
    (''6'', ''23''),
    (''7'', ''24''),
    (''7'', ''25'');

Mostrando todos los registros

SELECT * 
FROM `entity` e
LEFT JOIN `entity_attributevalue` ea ON ea.entity_id = e.id
LEFT JOIN `attributevalue` av ON ea.attributevalue_id = av.id
LEFT JOIN `attribute` a ON av.attribute_id = a.id;

id title author createdOn entity_id attributevalue_id id value attribute_id id name type 1 TestFile Joe 2011-01-01 00:00:00 1 1 1 true 1 1 ReadOnly bool 1 TestFile Joe 2011-01-01 00:00:00 1 2 2 xls 2 2 FileFormat text 1 TestFile Joe 2011-01-01 00:00:00 1 3 3 false 3 3 Private bool 1 TestFile Joe 2011-01-01 00:00:00 1 4 4 2011-10-03 4 4 LastModified date 2 LongNovel Mary 2011-02-01 00:00:00 2 5 5 true 1 1 ReadOnly bool 2 LongNovel Mary 2011-02-01 00:00:00 2 6 6 json 2 2 FileFormat text 2 LongNovel Mary 2011-02-01 00:00:00 2 7 7 true 3 3 Private bool 2 LongNovel Mary 2011-02-01 00:00:00 2 8 8 2011-10-04 4 4 LastModified date 3 ShortStory Susan 2011-03-01 00:00:00 3 9 9 false 1 1 ReadOnly bool 3 ShortStory Susan 2011-03-01 00:00:00 3 10 10 ascii 2 2 FileFormat text 3 ShortStory Susan 2011-03-01 00:00:00 3 11 11 false 3 3 Private bool 3 ShortStory Susan 2011-03-01 00:00:00 3 12 12 2011-10-01 4 4 LastModified date 4 ProfitLoss Bill 2011-04-01 00:00:00 4 13 13 false 1 1 ReadOnly bool 4 ProfitLoss Bill 2011-04-01 00:00:00 4 14 14 text 2 2 FileFormat text 4 ProfitLoss Bill 2011-04-01 00:00:00 4 15 15 true 3 3 Private bool 4 ProfitLoss Bill 2011-04-01 00:00:00 4 16 16 2011-10-02 4 4 LastModified date 5 MonthlyBudget George 2011-05-01 00:00:00 5 17 17 false 1 1 ReadOnly bool 5 MonthlyBudget George 2011-05-01 00:00:00 5 18 18 binary 2 2 FileFormat text 5 MonthlyBudget George 2011-05-01 00:00:00 5 19 19 false 3 3 Private bool 5 MonthlyBudget George 2011-05-01 00:00:00 5 20 20 2011-10-20 4 4 LastModified date 6 Paper Jane 2012-04-01 00:00:00 6 21 21 binary 2 2 FileFormat text 6 Paper Jane 2012-04-01 00:00:00 6 22 22 false 3 3 Private bool 6 Paper Jane 2012-04-01 00:00:00 6 23 23 2011-10-20 4 4 LastModified date 7 Essay John 2012-03-01 00:00:00 7 24 24 binary 2 2 FileFormat text 7 Essay John 2012-03-01 00:00:00 7 25 25 2011-10-20 4 4 LastModified date 8 Article Dan 2012-12-01 00:00:00 NULL NULL NULL NULL NULL NULL NULL NULL

tabla dinámica

SELECT e.*,
    MAX( IF(a.name = ''ReadOnly'', av.value, NULL) ) as ''ReadOnly'',
    MAX( IF(a.name = ''FileFormat'', av.value, NULL) ) as ''FileFormat'',
    MAX( IF(a.name = ''Private'', av.value, NULL) ) as ''Private'',
    MAX( IF(a.name = ''LastModified'', av.value, NULL) ) as ''LastModified''
FROM `entity` e
LEFT JOIN `entity_attributevalue` ea ON ea.entity_id = e.id
LEFT JOIN `attributevalue` av ON ea.attributevalue_id = av.id
LEFT JOIN `attribute` a ON av.attribute_id = a.id
GROUP BY e.id;

id title author createdOn ReadOnly FileFormat Private LastModified 1 TestFile Joe 2011-01-01 00:00:00 true xls false 2011-10-03 2 LongNovel Mary 2011-02-01 00:00:00 true json true 2011-10-04 3 ShortStory Susan 2011-03-01 00:00:00 false ascii false 2011-10-01 4 ProfitLoss Bill 2011-04-01 00:00:00 false text true 2011-10-02 5 MonthlyBudget George 2011-05-01 00:00:00 false binary false 2011-10-20 6 Paper Jane 2012-04-01 00:00:00 NULL binary false 2011-10-20 7 Essay John 2012-03-01 00:00:00 NULL binary NULL 2011-10-20 8 Article Dan 2012-12-01 00:00:00 NULL NULL NULL NULL

Answer 3

La forma general de tal consulta sería

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)
   ...

Por lo tanto, debe generar dinámicamente su consulta a partir de los atributos que necesita. En pseudocódigo php-ish

$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

La pregunta menciona a MySQL, y de hecho este DBMS tiene una función especial para este tipo de problema: GROUP_CONCAT(expr) . Eche un vistazo en el manual de referencia de MySQL sobre grupos por funciones . La función se agregó en MySQL versión 4.1. Utilizará GROUP BY FileID en la consulta.

No estoy seguro de cómo quieres que se vea el resultado. Si desea que cada atributo enumerado para cada elemento (incluso si no está configurado), será más difícil. Sin embargo, esta es mi sugerencia de cómo hacerlo:

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;

Esto le da todos los atributos en el mismo orden, lo que podría ser útil. La salida será como la siguiente:

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

De esta manera, solo necesita una única consulta DB, y la salida es fácil de analizar. Si desea almacenar los atributos como hora real, etc. en la base de datos, necesitaría usar SQL dinámico, pero me mantendría alejado de eso y almacenaría los valores en varchars.

Answer 5

Respuesta parcial ya que no conozco MySQL (bueno). En MSSQL miraría las tablas pivote o crearía una tabla temporal en un procedimiento almacenado. Puede ser un momento difícil ...

Answer 6

Si está buscando algo más útil (y que se pueda unir) que un resultado de grupo-concat, pruebe esta solución a continuación. He creado algunas tablas muy similares a su ejemplo para que esto tenga sentido.

Esto funciona cuando:

• Desea una solución SQL pura (sin código, sin bucles)
• Tienes un conjunto predecible de atributos (p. Ej., No dinámico)
• Está bien actualizar la consulta cuando se deben agregar nuevos tipos de atributos
• Preferiría un resultado al que se pueda UNIR, SIN UNIÓN o anidado como una subselección

Tabla A (Archivos)

FileID, Title, Author, CreatedOn

Tabla B (Atributos)

AttrID, AttrName, AttrType [not sure how you use type...]

Tabla C (Archivos_Attributes)

FileID, AttrID, AttrValue

Una consulta tradicional extraería muchas filas redundantes:

SELECT * FROM 
Files F 
LEFT JOIN Files_Attributes FA USING (FileID)
LEFT JOIN Attributes A USING (AttributeID);

AttrID FileID Title Author CreatedOn AttrValue AttrName AttrType 50 1 TestFile Joe 2011-01-01 true ReadOnly bool 60 1 TestFile Joe 2011-01-01 xls FileFormat text 70 1 TestFile Joe 2011-01-01 false Private bool 80 1 TestFile Joe 2011-01-01 2011-10-03 LastModified date 60 2 LongNovel Mary 2011-02-01 json FileFormat text 80 2 LongNovel Mary 2011-02-01 2011-10-04 LastModified date 70 2 LongNovel Mary 2011-02-01 true Private bool 50 2 LongNovel Mary 2011-02-01 true ReadOnly bool 50 3 ShortStory Susan 2011-03-01 false ReadOnly bool 60 3 ShortStory Susan 2011-03-01 ascii FileFormat text 70 3 ShortStory Susan 2011-03-01 false Private bool 80 3 ShortStory Susan 2011-03-01 2011-10-01 LastModified date 50 4 ProfitLoss Bill 2011-04-01 false ReadOnly bool 70 4 ProfitLoss Bill 2011-04-01 true Private bool 80 4 ProfitLoss Bill 2011-04-01 2011-10-02 LastModified date 60 4 ProfitLoss Bill 2011-04-01 text FileFormat text 50 5 MonthlyBudget George 2011-05-01 false ReadOnly bool 60 5 MonthlyBudget George 2011-05-01 binary FileFormat text 70 5 MonthlyBudget George 2011-05-01 false Private bool 80 5 MonthlyBudget George 2011-05-01 2011-10-20 LastModified date

Esta consulta coalescente (enfoque que usa MAX) puede fusionar las filas:

SELECT
F.*,
MAX( IF(A.AttrName = ''ReadOnly'', FA.AttrValue, NULL) ) as ''ReadOnly'',
MAX( IF(A.AttrName = ''FileFormat'', FA.AttrValue, NULL) ) as ''FileFormat'',
MAX( IF(A.AttrName = ''Private'', FA.AttrValue, NULL) ) as ''Private'',
MAX( IF(A.AttrName = ''LastModified'', FA.AttrValue, NULL) ) as ''LastModified''
FROM 
Files F 
LEFT JOIN Files_Attributes FA USING (FileID)
LEFT JOIN Attributes A USING (AttributeID)
GROUP BY
F.FileID;

FileID Title Author CreatedOn ReadOnly FileFormat Private LastModified 1 TestFile Joe 2011-01-01 true xls false 2011-10-03 2 LongNovel Mary 2011-02-01 true json true 2011-10-04 3 ShortStory Susan 2011-03-01 false ascii false 2011-10-01 4 ProfitLoss Bill 2011-04-01 false text true 2011-10-02 5 MonthlyBudget George 2011-05-01 false binary false 2011-10-20

Answer 7

Sin embargo, hay soluciones para usar líneas como columnas, también transponer los datos. Implica trucos de consulta para hacerlo en SQL puro, o tendrá que confiar en ciertas características solo disponibles en cierta base de datos, utilizando tablas pivote (o tablas cruzadas).

Como ejemplo, puede ver cómo hacer esto aquí en Oracle (11g).

La versión de programación será más simple de mantener y crear y, además, funcionará con cualquier base de datos.