Re: Database structure for a "Document Management System"

[Jacky Shu]

it is not a right approach to left a huge table 11 times.
instead, use something like this:


SELECT D.id,
IF(DFL.document_field_id = 1, DFL.value, null),
IF(DFL.document_field_id = 2, DFL.value, null),
...
FROM documents AS D
LEFT JOIN document_fields_link AS DFL ON DFL1.document_id = D.id
group BY d.document_id



Michelangelo Rezzonico Wrote:
-------------------------------------------------------
> Hi felix,
>
> > Not everyone would agree with you. Search the
> web for "entity attribute value"
> What do you mean ?
> The "best" solution is the second ?
> In the second solution I see the advantage that I
> can create views that are modifiable.
> With the third solution I can create views, but
> are not modifiable.
> What is your opinion ?
> I am very interested in you opinion.
>
> > Your query might benefit from a composite index
> on (document_field_id, document_id).
> Thanks a lot for your answer !!
> Problem solved.
>
> --------------------------------------------------
> -------------------------------
> If you think that the best solution is the third I
> have another problem (perhaps you can help me).
> I have the following 3 queries (and the
> corresponding output of the describe command).
> The differences between this queries is only in
> the last three lines.
> The first and the second queries are very fast,
> but the third not.
> Probably the reason is here "Using where; Using
> temporary; Using filesort"
> I suppose that this is "normal".
> Can you confirm this ?
>
> Best Regards. Michelangelo
>
>
> 1. Query
>
> SELECT D.id,
> DFL1.value,
> DFL2.value,
> DFL3.value,
> DFL4.value,
> DFL5.value,
> DFL6.value,
> DFL7.value,
> DFL8.value,
> DFL9.value,
> DFL10.value,
> DFL11.value
> FROM documents AS D
> LEFT JOIN document_fields_link AS DFL1 ON
> DFL1.document_id = D.id
> LEFT JOIN document_fields_link AS DFL2 ON
> DFL2.document_id = D.id
> LEFT JOIN document_fields_link AS DFL3 ON
> DFL3.document_id = D.id
> LEFT JOIN document_fields_link AS DFL4 ON
> DFL4.document_id = D.id
> LEFT JOIN document_fields_link AS DFL5 ON
> DFL5.document_id = D.id
> LEFT JOIN document_fields_link AS DFL6 ON
> DFL6.document_id = D.id
> LEFT JOIN document_fields_link AS DFL7 ON
> DFL7.document_id = D.id
> LEFT JOIN document_fields_link AS DFL8 ON
> DFL8.document_id = D.id
> LEFT JOIN document_fields_link AS DFL9 ON
> DFL9.document_id = D.id
> LEFT JOIN document_fields_link AS DFL10 ON
> DFL10.document_id = D.id
> LEFT JOIN document_fields_link AS DFL11 ON
> DFL11.document_id = D.id
> WHERE DFL1.document_field_id = 1
> AND DFL2.document_field_id = 2
> AND DFL3.document_field_id = 3
> AND DFL4.document_field_id = 4
> AND DFL5.document_field_id = 5
> AND DFL6.document_field_id = 6
> AND DFL7.document_field_id = 7
> AND DFL8.document_field_id = 8
> AND DFL9.document_field_id = 9
> AND DFL10.document_field_id = 10
> AND DFL11.document_field_id = 11
> ORDER BY DFL1.value
> LIMIT 10;
>
> | 1 | SIMPLE | DFL1 | ref | index2
> | index2 | 4 | const
> | 97534 | Using where; Using filesort |
> | 1 | SIMPLE | DFL11 | ref | index2
> | index2 | 8 | const,dms.DFL1.document_id
> | 1 | Using where |
> | 1 | SIMPLE | DFL4 | ref | index2
> | index2 | 8 | const,dms.DFL11.document_id
> | 1 | Using where |
> | 1 | SIMPLE | DFL2 | ref | index2
> | index2 | 8 | const,dms.DFL4.document_id
> | 1 | Using where |
> | 1 | SIMPLE | DFL3 | ref | index2
> | index2 | 8 | const,dms.DFL11.document_id
> | 1 | Using where |
> | 1 | SIMPLE | DFL5 | ref | index2
> | index2 | 8 | const,dms.DFL4.document_id
> | 1 | Using where |
> | 1 | SIMPLE | DFL6 | ref | index2
> | index2 | 8 | const,dms.DFL11.document_id
> | 1 | Using where |
> | 1 | SIMPLE | DFL7 | ref | index2
> | index2 | 8 | const,dms.DFL1.document_id
> | 1 | Using where |
> | 1 | SIMPLE | DFL8 | ref | index2
> | index2 | 8 | const,dms.DFL11.document_id
> | 1 | Using where |
> | 1 | SIMPLE | DFL9 | ref | index2
> | index2 | 8 | const,dms.DFL11.document_id
> | 1 | Using where |
> | 1 | SIMPLE | DFL10 | ref | index2
> | index2 | 8 | const,dms.DFL1.document_id
> | 1 | Using where |
> | 1 | SIMPLE | D | eq_ref | id
> | id | 4 | dms.DFL6.document_id
> | 1 | Using where |
>
>
> 2. Query
>
> SELECT D.id,
> DFL1.value,
> DFL2.value,
> DFL3.value,
> DFL4.value,
> DFL5.value,
> DFL6.value,
> DFL7.value,
> DFL8.value,
> DFL9.value,
> DFL10.value,
> DFL11.value
> FROM documents AS D
> LEFT JOIN document_fields_link AS DFL1 ON
> DFL1.document_id = D.id
> LEFT JOIN document_fields_link AS DFL2 ON
> DFL2.document_id = D.id
> LEFT JOIN document_fields_link AS DFL3 ON
> DFL3.document_id = D.id
> LEFT JOIN document_fields_link AS DFL4 ON
> DFL4.document_id = D.id
> LEFT JOIN document_fields_link AS DFL5 ON
> DFL5.document_id = D.id
> LEFT JOIN document_fields_link AS DFL6 ON
> DFL6.document_id = D.id
> LEFT JOIN document_fields_link AS DFL7 ON
> DFL7.document_id = D.id
> LEFT JOIN document_fields_link AS DFL8 ON
> DFL8.document_id = D.id
> LEFT JOIN document_fields_link AS DFL9 ON
> DFL9.document_id = D.id
> LEFT JOIN document_fields_link AS DFL10 ON
> DFL10.document_id = D.id
> LEFT JOIN document_fields_link AS DFL11 ON
> DFL11.document_id = D.id
> WHERE DFL1.document_field_id = 1
> AND DFL2.document_field_id = 2
> AND DFL3.document_field_id = 3
> AND DFL4.document_field_id = 4
> AND DFL5.document_field_id = 5
> AND DFL6.document_field_id = 6
> AND DFL7.document_field_id = 7
> AND DFL8.document_field_id = 8
> AND DFL9.document_field_id = 9
> AND DFL10.document_field_id = 10
> AND DFL11.document_field_id = 11
> AND D.document_type_id = 2
> LIMIT 10;
>
> | 1 | SIMPLE | DFL4 | ref | index2
> | index2 | 4 |
> const | 97533 | Using where
> |
> | 1 | SIMPLE | DFL11 | ref | index2
> | index2 | 8 |
> const,dms.DFL4.document_id | 1 | Using where
> |
> | 1 | SIMPLE | DFL1 | ref | index2
> | index2 | 8 |
> const,dms.DFL11.document_id | 1 | Using where
> |
> | 1 | SIMPLE | DFL2 | ref | index2
> | index2 | 8 |
> const,dms.DFL11.document_id | 1 | Using where
> |
> | 1 | SIMPLE | DFL3 | ref | index2
> | index2 | 8 |
> const,dms.DFL11.document_id | 1 | Using where
> |
> | 1 | SIMPLE | DFL5 | ref | index2
> | index2 | 8 |
> const,dms.DFL4.document_id | 1 | Using where
> |
> | 1 | SIMPLE | DFL6 | ref | index2
> | index2 | 8 |
> const,dms.DFL11.document_id | 1 | Using where
> |
> | 1 | SIMPLE | DFL7 | ref | index2
> | index2 | 8 |
> const,dms.DFL1.document_id | 1 | Using where
> |
> | 1 | SIMPLE | DFL8 | ref | index2
> | index2 | 8 |
> const,dms.DFL11.document_id | 1 | Using where
> |
> | 1 | SIMPLE | DFL9 | ref | index2
> | index2 | 8 |
> const,dms.DFL11.document_id | 1 | Using where
> |
> | 1 | SIMPLE | DFL10 | ref | index2
> | index2 | 8 |
> const,dms.DFL1.document_id | 1 | Using where
> |
> | 1 | SIMPLE | D | eq_ref |
> id,fk_document_type_id,document_type_id | id |
> 4 | dms.DFL8.document_id | 1 |
> Using where |
>
>
> 3. Query
>
> SELECT D.id,
> DFL1.value,
> DFL2.value,
> DFL3.value,
> DFL4.value,
> DFL5.value,
> DFL6.value,
> DFL7.value,
> DFL8.value,
> DFL9.value,
> DFL10.value,
> DFL11.value
> FROM documents AS D
> LEFT JOIN document_fields_link AS DFL1 ON
> DFL1.document_id = D.id
> LEFT JOIN document_fields_link AS DFL2 ON
> DFL2.document_id = D.id
> LEFT JOIN document_fields_link AS DFL3 ON
> DFL3.document_id = D.id
> LEFT JOIN document_fields_link AS DFL4 ON
> DFL4.document_id = D.id
> LEFT JOIN document_fields_link AS DFL5 ON
> DFL5.document_id = D.id
> LEFT JOIN document_fields_link AS DFL6 ON
> DFL6.document_id = D.id
> LEFT JOIN document_fields_link AS DFL7 ON
> DFL7.document_id = D.id
> LEFT JOIN document_fields_link AS DFL8 ON
> DFL8.document_id = D.id
> LEFT JOIN document_fields_link AS DFL9 ON
> DFL9.document_id = D.id
> LEFT JOIN document_fields_link AS DFL10 ON
> DFL10.document_id = D.id
> LEFT JOIN document_fields_link AS DFL11 ON
> DFL11.document_id = D.id
> WHERE DFL1.document_field_id = 1
> AND DFL2.document_field_id = 2
> AND DFL3.document_field_id = 3
> AND DFL4.document_field_id = 4
> AND DFL5.document_field_id = 5
> AND DFL6.document_field_id = 6
> AND DFL7.document_field_id = 7
> AND DFL8.document_field_id = 8
> AND DFL9.document_field_id = 9
> AND DFL10.document_field_id = 10
> AND DFL11.document_field_id = 11
> AND D.document_type_id = 2
> ORDER BY DFL1.value
> LIMIT 10;
>
> | 1 | SIMPLE | D | range |
> id,fk_document_type_id,document_type_id |
> document_type_id | 4 | NULL
> | 99997 | Using where; Using temporary;
> Using filesort |
> | 1 | SIMPLE | DFL11 | ref | index2
> | index2 | 8
> | const,dms.D.id | 1 | Using
> where |
> | 1 | SIMPLE | DFL1 | ref | index2
> | index2 | 8
> | const,dms.DFL11.document_id | 1 | Using
> where |
> | 1 | SIMPLE | DFL2 | ref | index2
> | index2 | 8
> | const,dms.D.id | 1 | Using
> where |
> | 1 | SIMPLE | DFL3 | ref | index2
> | index2 | 8
> | const,dms.DFL11.document_id | 1 | Using
> where |
> | 1 | SIMPLE | DFL5 | ref | index2
> | index2 | 8
> | const,dms.D.id | 1 | Using
> where |
> | 1 | SIMPLE | DFL6 | ref | index2
> | index2 | 8
> | const,dms.D.id | 1 | Using
> where |
> | 1 | SIMPLE | DFL7 | ref | index2
> | index2 | 8
> | const,dms.DFL1.document_id | 1 | Using
> where |
> | 1 | SIMPLE | DFL8 | ref | index2
> | index2 | 8
> | const,dms.D.id | 1 | Using
> where |
> | 1 | SIMPLE | DFL9 | ref | index2
> | index2 | 8
> | const,dms.DFL11.document_id | 1 | Using
> where |
> | 1 | SIMPLE | DFL10 | ref | index2
> | index2 | 8
> | const,dms.DFL1.document_id | 1 | Using
> where |
> | 1 | SIMPLE | DFL4 | ref | index2
> | index2 | 8
> | const,dms.DFL7.document_id | 1 | Using
> where |