I have a problem concerning the correct design of a relationship. My database has only one table called products, which has the following columns:

id, name, categories, price, description, extra data

And I need to display the info of categories but the problem is this:

I have the categories saved as one string all together.

  • category1, category2, category3, category4

I understand I need to create the relationship-table but I don't see how to call the info.

For example, if I create

table: relcat
iditem (from table products)
idcategory (from table category)

then how do I move the values from the column categories from table products to the table categories?

  • You must create link table (product_ID, category). It allows you to create foreign key constraints. The category field will be removed from table's structure at all, of course. – Akina Aug 10 at 4:43
  • Look up first normal form. In particular look up why keyed access to all data is a good thing. From keyed access, move on to indexed access. If you have several thousand products, your searches will take seconds if you have keyed and indexed access, and will take an hour if you do table scans. – Walter Mitty Aug 10 at 15:19
up vote 0 down vote accepted

OK; you've discovered that having multiple data values in a single field is a Bad Idea. Good. Let's move on.

You need a second table to hold the catagories that relate to a particular product. Given that categories are shared across many products, you probably want a "master" table of categories as well.

select * from categories ; 
+----+------+
| id | name | 
+----+------+
|  1 | Cat1 |
|  2 | Cat1 |
+----+------+

select * from products ; 
+----+--------+-------+-------------+
| id | name   | price | description | 
+----+--------+-------+-------------+
| 11 | Fred   |  1.99 | Flintstone  | 
| 22 | Barney |  2.99 | Rubble      | 
+----+--------+-------+-------------+

select * from prod_cats ; 
+---------+--------+
| prod_id | cat_id | 
+---------+--------+
|      11 |      1 | 
|      22 |      1 | 
|      22 |      2 | 
+---------+--------+

To find products with a given category:

select p.name 
from       products p 
inner join prod_cats pc 
      on   p.id = pc.prod_id 
inner join categories c 
      on   pc.cat_id = c.id 
where c.name = 'Cat1' 
order by 1 ; 

+--------+
| name   |
+--------+
| Barney | 
| Fred   | 
+--------+

To find products with all given categories:

select p.name 
from products p 
where p.id in 
( 
  select pc.prod_id 
  from       prod_cats pc 
  inner join categories c 
        on   pc.cat_id = c.id 
  where c.name in ( 'Cat1', 'Cat2' )   /* Categories to match */ 
  group by pc.prod_id 
  having count( pc.cat_id ) = 2        /* Number of matches required */ 
) t0 
order by 1 ; 

+--------+
| name   |
+--------+
| Barney | 
+--------+
  • I don't think this is the answer. Rederick already knows he has to create a "link"-table. His question is: how do I move the categories from the "bad" column categories in the products-table to the "good" link-table. So he is asking for a script or stored_procedure to do this normalisation. As it is likely to use cursors and iterations this will depend on the type of rdbms (mysql? sqlserver? oracle? ...) – luc Aug 10 at 14:21
  • he already help me a lot, i made a php script for move all the categories and now is all right – rederick Aug 10 at 18:35

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.