Storage of categories



  • Here's the code I've been dealing with for the past few weeks:

     14:28:49 (9) > describe bg_listings;
    +-------------------------+--------------+------+-----+---------+----------------+
    | Field                   | Type         | Null | Key | Default | Extra          |
    +-------------------------+--------------+------+-----+---------+----------------+
    | id                      | int(11)      | NO   | PRI | NULL    | auto_increment |
    | cust_id                 | int(11)      | NO   | MUL | 1       |                |
    | category                | varchar(255) | YES  |     | NULL    |                |
    | cat_id                  | int(11)      | YES  |     | 0       |                |
    +-------------------------+--------------+------+-----+---------+----------------+

    The rest of the table is of no concern. Here's a bit of data stored in bg_listings:

     14:30:47 (10) > select * from bg_listings where id = 280\G
    *************************** 1. row ***************************
    id: 280
    cust_id: 228
    category: xCustomer Feedback,xMystery Shopping,xOnline Services
    cat_id: 0
    1 row in set (0.00 sec)

    Please note that this table is not a view, this is the actual data. Then we go to the code:

    function getAltCatTitle($names){
    global $db,$SITE;

    $titles = array();
    $names = str_replace(',','|',str_replace(',x',',',preg_replace('/^x/','',$names)));

    $sql = "SELECT cat_name as title FROM bg_categories WHERE cat_name REGEXP '^.({$names}).$'";

    foreach($db->get_results($sql) as $title) array_push($titles,trim($title->title));
    $titles = implode(", ",$titles);
    return $titles;
    }

    and

    $catfilter = "(category REGEXP '^x".$cat_name."$' OR category LIKE '%   ,x".$cat_name.",%' OR category REGEXP '^x".$cat_name.",' OR category REGEXP ',x".$cat_name."$')";

    and

    $linktocat = preg_replace("/^x/", "", $linktocat);

    and

    if($cid = $db->get_row("SELECT cat_id, parent_id FROM bg_categories WHERE cat_name = '$linktocat' AND site_id = $SITE",ARRAY_N)) {

    Seriously, who's idea was it to store all of the categories in a single, character field, and have them all prepended by 'x'?



  •  Joys of 0NF. Well, -1NF this time.



  • @gms8994 said:

    Seriously, who's idea was it to store all of the categories in a single, character field, and have them all prepended by 'x'?
    Exactly.  Why not go all the way and just use 'xml' instead of just 'x'?

    That may not be enough, by itself.  You'd probably need a few web services, some flat files and ftp, smtp transfers, then you're talking serious business.

    Actually, these constructs make me think that someone saw a bitfield and epically failed by trying to store categorical data that way.


Log in to reply
 

Looks like your connection to What the Daily WTF? was lost, please wait while we try to reconnect.