MultiClassification

Requirements

Solution

Separate Classifications records are used for every classification which is set. No empty records used, a document has as many Classifications records as many classifications are set.

Detail

No problems determined until now. A smarty loop goes through all Classifications records to display them line to line.

New/Update

For every aspects defined in table Aspects at least one line appears even if it is empty. At the right side of all line there is a new button to open additional line for entering classification in the same aspect, and a delete button to remove that classification. If there is no more classification in an aspect (after deleting the last one) an empty line appears.

aspect X

code

AX_L1_0

AX_L2_0

...

from - to

new/delete

code

AX_L1_1

AX_L2_1

...

from - to

new/delete

Searching is a performance issue. Because of all Classifications are different records a large joins could be used to perform searching, which can be very slow. The following optimization steps will be implemented:

  1. determine first the number of Classifications records involved in one aspect value search

  2. perform a nested select with the smallest records set in the inner most level to limit the involved records to the minimum

  3. read DrawingsRevisionsJoinSet with the id set got from the select above

E.g.: assumed that we are looking for documents with aspect codes 22, 345 and 42, the steps above: 

 1. select code, count(*) from Classifications where code in ( '22', '345', '42' ) order by 2;

 +------+----------+
 | code | count(*) |
 +------+----------+
 |  345 |       10 |
 |   42 |      234 |
 |   22 |     1410 |
 +------+----------+

 2. select objectId from Classifications where objectTypeId = ... and code = '22' and object Id in (
      select objectId from Classifications where objectTypeId = ... and code = '42' and object Id in (
        select objectId from Classifications where objectTypeId = ... and code = '345'
      )
    )

List

Displaying of classifications in a document list is also a performance issue. To be able to display classification information quickly a static database table will be used to store some information about classified documents:

Object Id

Object Type

aspect 0

...

aspect 9

min code

max code

full code

count

from

to

...

min code

max code

full code

count

from

to

There is one record for one object (Drawing, Task). The field full code is a comma separated list of all aspect values in the appropriate aspect assigned to the object. What's about from and to???? On a document list with classifications the min code, max code can appear according the sorting order. On a non sorted list the full code can appear or it can be a site setting parameter.

This table will be updated on regularly basis or database procedures fired by triggers on the tables Drawings, Comments and Classifications or by application transactions?

Copyright 2008, SoftXS GmbH, Switzerland