Wiki Navigation

Categorieën

Model

!Structurele problemen!

De tabel section_category bevat entries met category_id = 0. Deze category bestaat niet in de tabel categories waardoor de integriteit van deze tabel verloren gaat. Meer uitleg over referential integrity: http://en.wikipedia.org/wiki/Referential_integrity

De huidige key van de tabel is (section_id,category_id,uid), wat betekent dat volgende entries mogelijk zijn:

+------------+-------------+-----+----------+
| section_id | category_id | uid | position |
+------------+-------------+-----+----------+
|          1 |           1 |   1 |        0 |
|          1 |           2 |   1 |        0 |
|          1 |           3 |   1 |        0 |
+------------+-------------+-----+----------+

De gebruiker 1 heeft sectie 1 in zowel category 1,2 en 3. Terwijl de relatie van deze entiteiten is dat een gebruiker 1 sectie in 1 categorie kan onderbrengen. De primary key op deze tabel zou moeten zijn (section_id,category_id). De kolom uid is overbodig want de rijen kunnen geidentificeerd worden aan de hand van user_id in categories en section_user.

De query om alle secties van een gebruiker op te halen per categorie is nu:

(
        SELECT su.`permission` , s.`xml_uri` , s.`title` AS `section_title` , s.`author_id` , s.`id` AS `section_id` ,
                        sc.`category_id` , c.`title` , c.`position` AS `position` , c.`collapsed` , sc.`position` AS `section_position`
        FROM `section_category` sc
        LEFT JOIN `categories` c ON c.`id` = `sc`.`category_id`
        LEFT JOIN `section_user` su ON su.`section_id` = sc.`section_id` AND su.`user_id` = 126
        JOIN `section` s ON s.`id` = sc.`section_id`
        WHERE sc.`uid` = 126
        )
        UNION
        (
        SELECT su.`permission` , NULL AS `xml_uri` , NULL AS `section_title` , NULL AS `author_id` , NULL AS `section_id` ,
                        c.`id` AS `category_id` , c.`title` , c.`position` AS `position` , c.`collapsed` , sc.`position` AS `section_position`
        FROM `categories` c
        LEFT JOIN `section_category` sc ON c.`id` = `sc`.`category_id` AND sc.`uid` = 126
        LEFT JOIN `section_user` su ON su.`section_id` = sc.`section_id`
        WHERE c.`uid` = 126
        AND sc.section_id is NULL
        )
        ORDER BY `position` ASC , `section_position`;

Dit kan ook op een meer eenvoudig en duidelijkere manier opgehaald worden (reeds rekening houdend met de aanpassing van het weglaten van 0 als categorieloos en uid verwijderd):

(SELECT `su`.`permission` , `s`.`xml_uri` , `s`.`title` AS `section_title` , `s`.`author_id` , `s`.`id` AS `section_id` , 
                `sc`.`category_id` , `c`.`title` , `c`.`position` AS `position` , `c`.`collapsed` , `sc`.`position` AS `section_position` FROM `section_user` `su`
 LEFT JOIN (`section_category` `sc`, `categories` `c`) ON (`su`.`section_id` = `sc`.`section_id` AND `sc`.`category_id` = `c`.`id` AND `c`.`uid` = 126)
 LEFT JOIN `section` `s` ON `su`.`section_id` = `s`.`id`
 WHERE `su`.`user_id` = 126 AND `s`.`id` IS NOT NULL)
UNION
(SELECT NULL AS `permission` , NULL AS `xml_uri` , NULL AS `section_title` , NULL AS `author_id` , NULL AS `section_id` , 
                    c.`id` AS `category_id` , c.`title` , c.`position` AS `position` , c.`collapsed`, NULL AS `section_position` FROM `categories` `c`
 LEFT JOIN `section_category` `sc` ON `c`.`id` = `sc`.`category_id` 
 WHERE `c`.`uid` = 126 AND `sc`.`category_id` IS NULL) 
ORDER BY `position` ASC, `section_position`;

Note: 's'.'id' IS NOT NULL is te wijten aan invalid data aanwezig in section_user waar section_id entries aanwezig zijn die niet meer bestaan in section.

Query

Haal alle secties op van een user via de tabel section_user:

SELECT `su`.* FROM `section_user` `sc` WHERE `su`.`user_id` = 126
73 rows in set (0.00 sec)

Breidt de resultaten uit met entries uit section_category, d.w.z. zoek de categorie voor de gevonden secties uit section_user :

SELECT `su`.*,`sc`.* FROM `section_user` `su` LEFT JOIN `section_category` `sc` ON `su`.`section_id` = `sc`.`section_id` WHERE `su`.`user_id` = 126;
116 rows in set (0.00 sec)

Omdat de relatie zonder bovenstaande wijziging toelaat dat secties tot categorieen behoren die over meerdere gebruikers worden gedeeld (de 0 categorie) is de resultset uitgebreid naar 116 rijen.

Dit probleem wordt opgelost door de query uit te breiden naar de categories tabel waar de gegevens voor gevonden category_id worden aangevuld:

SELECT `su`.*,`sc`.*,`c`.* FROM `section_user` `su` 
LEFT JOIN (`section_category` `sc`, `categories` `c`) ON (`su`.`section_id` = `sc`.`section_id` AND `sc`.`category_id` = `c`.`id` AND `c`.`uid` = 126)
WHERE `su`.`user_id` = 126;
73 rows in set (0.00 sec)

Om de detailgegevens van de secties op te vragen dient de tabel section toegevoegd te worden:

SELECT `su`.*,`sc`.*,`c`.*,`s`.* FROM `section_user` `su`
 LEFT JOIN (`section_category` `sc`, `categories` `c`) ON (`su`.`section_id` = `sc`.`section_id` AND `sc`.`category_id` = `c`.`id` AND `c`.`uid` = 126)
 LEFT JOIN `section` `s` ON `su`.`section_id` = `s`.`id`
 WHERE `su`.`user_id` = 126;
73 rows in set (0.00 sec)

Uit de resultaten kunnen NULL gegevens gevonden worden bij de details van de sectie, dit komt door inconsistentie van de data in section_user waar verwijzingen staan naar section_ids die niet meer bestaan in de section tabel. Dit wordt opgelost door te controleren op section_id via section tabel:

SELECT `su`.*,`sc`.*,`c`.*,`s`.* FROM `section_user` `su`
 LEFT JOIN (`section_category` `sc`, `categories` `c`) ON (`su`.`section_id` = `sc`.`section_id` AND `sc`.`category_id` = `c`.`id` AND `c`.`uid` = 126)
 LEFT JOIN `section` `s` ON `su`.`section_id` = `s`.`id`
 WHERE `su`.`user_id` = 126 AND `s`.`id` IS NOT NULL;
48 rows in set (0.00 sec)

Om de resultaten uit te breiden met categorieen die geen secties bevatten dient een union uitgevoerd te worden op de categories tabel waar de category_id niet voorkomt in section_category:

SELECT `c`.* FROM `categories` `c` LEFT JOIN `section_category` `sc` ON `c`.`id` = `sc`.`category_id`  WHERE `c`.`uid` = 126 AND `sc`.`category_id` IS NULL

Performance query

Oude query:

+----+--------------+------------+--------+--------------------+-----------------+---------+----------------------------+------+-------------------------+
| id | select_type  | table      | type   | possible_keys      | key             | key_len | ref                        | rows | Extra                   |
+----+--------------+------------+--------+--------------------+-----------------+---------+----------------------------+------+-------------------------+
|  1 | PRIMARY      | s          | ALL    | PRIMARY            | NULL            | NULL    | NULL                       | 5459 |                         |
|  1 | PRIMARY      | sc         | ref    | PRIMARY            | PRIMARY         | 4       | curios.s.id                |    1 | Using where             |
|  1 | PRIMARY      | c          | eq_ref | PRIMARY            | PRIMARY         | 4       | curios.sc.category_id      |    1 |                         |
|  1 | PRIMARY      | su         | eq_ref | PRIMARY,section_id | PRIMARY         | 8       | const,curios.sc.section_id |    1 |                         |
|  2 | UNION        | c          | ref    | uid                | uid             | 4       | const                      |    2 |                         |
|  2 | UNION        | sc         | ref    | idx_category_id    | idx_category_id | 4       | curios.c.id                | 1326 | Using where; Not exists |
|  2 | UNION        | su         | ref    | section_id         | section_id      | 4       | curios.sc.section_id       |    1 |                         |
| NULL | UNION RESULT | <union1,2> | ALL    | NULL               | NULL            | NULL    | NULL                       | NULL | Using filesort          |
+----+--------------+------------+--------+--------------------+-----------------+---------+----------------------------+------+-------------------------+

Nieuwe query:

+----+--------------+------------+--------+-------------------------+-----------------+---------+----------------------------------+------+--------------------------------------+
| id | select_type  | table      | type   | possible_keys           | key             | key_len | ref                              | rows | Extra                                |
+----+--------------+------------+--------+-------------------------+-----------------+---------+----------------------------------+------+--------------------------------------+
|  1 | PRIMARY      | su         | ref    | PRIMARY,section_id      | PRIMARY         | 4       | const                            |   73 |                                      |
|  1 | PRIMARY      | c          | ref    | PRIMARY,uid             | uid             | 4       | const                            |    0 |                                      |
|  1 | PRIMARY      | sc         | ref    | PRIMARY,idx_category_id | PRIMARY         | 8       | curios.su.section_id,curios.c.id |    1 |                                      |
|  1 | PRIMARY      | s          | eq_ref | PRIMARY                 | PRIMARY         | 8       | curios.su.section_id             |    1 | Using where                          |
|  2 | UNION        | c          | ref    | uid                     | uid             | 4       | const                            |    2 |                                      |
|  2 | UNION        | sc         | ref    | idx_category_id         | idx_category_id | 4       | curios.c.id                      | 1326 | Using where; Using index; Not exists |
| NULL | UNION RESULT | <union1,2> | ALL    | NULL                    | NULL            | NULL    | NULL                             | NULL | Using filesort                       |
+----+--------------+------------+--------+-------------------------+-----------------+---------+----------------------------------+------+--------------------------------------+

Integriteit

Controleer de integriteit van de bovenstaande tabellen m.b.v. de volgende queries:

uid in de tabel categories moet bestaan in de user tabel:

SELECT COUNT( `c`.`uid` )
 FROM `categories` `c`
 LEFT JOIN `user` `u` ON `c`.`uid` = `u`.`id`
 WHERE `u`.`id` IS NULL

category_id in de tabel section_category moet bestaan in de categories tabel, tenzij category_id == 0 (categorieloze sectie):

SELECT COUNT( `sc`.`category_id` )
 FROM `section_category` `sc`
 LEFT JOIN `categories` `c` ON `sc`.`category_id` = `c`.`id`
 WHERE `sc`.`category_id` != 0 AND `c`.`id` IS NULL

section_id in de tabel section_category moet bestaan in de section tabel:

SELECT COUNT( `sc`.`section_id` )
FROM `section_category` `sc`
LEFT JOIN `section` `s` ON `sc`.`section_id` = `s`.`id`
WHERE `s`.`id` IS NULL

uid in de tabel section_category moet bestaan in de user tabel:

SELECT COUNT( `sc`.`uid` )
 FROM `section_category` `sc`
 LEFT JOIN `user` `u` ON `sc`.`uid` = `u`.`id`
 WHERE `u`.`id` IS NULL

author_id in de tabel section moet bestaan in de user tabel:

SELECT COUNT( `s`.`author_id` )
 FROM `section` `s`
 LEFT JOIN `user` `u` ON `s`.`author_id` = `u`.`id`
 WHERE `u`.`id` IS NULL

section_id in de tabel section_user moet bestaan in de section tabel:

SELECT COUNT( `su`.`section_id` )
FROM `section_user` `su`
LEFT JOIN `section` `s` ON `su`.`section_id` = `s`.`id`
WHERE `s`.`id` IS NULL

user_id in de tabel section_user moet bestaan in de user tabel:

SELECT COUNT( `su`.`user_id` )
 FROM `section_user` `su`
 LEFT JOIN `user` `u` ON `su`.`user_id` = `u`.`id`
 WHERE `u`.`id` IS NULL

Download in other formats:

Plain Text