Storage of categories

gms8994

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'?

mol1111

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

boomzilla

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