MySQL Forums
Forum List  »  Newbie

Re: challenging problem -spliting different values within one field
Posted by: Phillip Ward
Date: April 07, 2014 05:59AM

More than one value in a single field? Breaks basic rules of Data Normalisation and will cause you major grief if you don't stamp it out early. Break the category out into another table, as in :

select * from catalog ; 

+-------------+----------------+ 
| sku         | product_name   | 
+-------------+----------------+ 
| LG123456789 | LG TV 40       | 
| AL123456789 | iPod           | 
| CL123456789 | Blue Ballerina | 
| RL123456789 | Belt           | 
+-------------+----------------+ 

select * from catalog_category ; 

+-------------+-------------+ 
| sku         | id_category | 
+-------------+-------------+ 
| LG123456789 |          15 | 
| LG123456789 |          10 | 
| LG123456789 |          14 | 
| AL123456789 |          12 | 
| AL123456789 |          11 | 
| AL123456789 |          10 | 
| CL123456789 |           1 | 
| CL123456789 |           6 | 
| CL123456789 |           8 | 
| RL123456789 |           2 | 
| RL123456789 |           1 | 
| RL123456789 |           5 | 
+-------------+-------------+

Your categories are going to cause you problems as well.
You seem to be relying on the sequential order of your categories to provide some kind of ordering/ hierarchy:

1, Fashion, 0 
   2, Men's Fashion, 1 
      3, Men's Clothes, 2 
      4, Men's Shoes, 2

This is not going to serve you well in the long term because you're inevitably going to have to add new category [id's] and that will mean inserting them into the current sequence and renumbering the ones you already have - and that's a big "no-no" on Planet Database (i.e. hugely inefficient and potentially risky).

If you want a hierarchy of Categories then store the parent category id for each one, as in:

select * from category_tree ; 

+----+---------------------+-----------+ 
| id | title               | parent_id | 
+----+---------------------+-----------+ 
|  1 | Fashion             |         0 | 
|  6 | Women's Fashion     |         1 | 
|  7 | Women's Clothes     |         6 | 
|  8 | Women's Shoes       |         6 | 
|  9 | Women's Accessories |         6 | 
| 10 | Electronics         |         0 | 
| 11 | Audio               |        10 | 
| 12 | Audio Accessories   |        11 | 
+----+---------------------+-----------+

Regards, Phill W.

Options: ReplyQuote


Subject
Written By
Posted
Re: challenging problem -spliting different values within one field
April 07, 2014 05:59AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.