List:General Discussion« Previous MessageNext Message »
From:Rhino Date:October 18 2005 6:54pm
Subject:Re: one product in more categories
View as plain text  
----- Original Message ----- 
From: <afan@stripped>
To: <mysql@stripped>
Sent: Tuesday, October 18, 2005 1:50 PM
Subject: one product in more categories


> Hi to all!
>
> I have tables products and categories
>
> CREATE TABLE categories (
>   cat_id INT(6) NOT NULL AUTO_INCREMENT,
>   cat_name VARCHAR(45) NULL,
>   cat_description TEXT NULL,
>   cat_parent INTEGER(4) UNSIGNED NULL,
>   cat_status ENUM(0,1) NULL DEFAULT 0,
>   PRIMARY KEY(cat_id),
>   INDEX ac_categories_index1(cat_status)
> );
>
> CREATE TABLE products (
>   product_id INTEGER(8) UNSIGNED NOT NULL AUTO_INCREMENT,
>   product_no VARCHAR(12) NULL,
>   product_name VARCHAR(45) NULL,
>   product_description TEXT NULL,
>   product_colors TEXT NULL,
>   product_includes TEXT NULL,
>   product_catalog VARCHAR(45) NULL,
>   product_status ENUM(0,1) NULL,
>   product_supplier VARCHAR(45) NULL,
>   product_start_date DATE NULL,
>   product_exp_date DATE NULL,
>   product_on_sale ENUM(0,1) NULL,
>   PRIMARY KEY(product_id),
>   INDEX ac_products_index1(product_start_date, product_exp_date),
>   INDEX ac_products_index2(product_status),
>   INDEX ac_products_index_onsale(product_on_sale)
> );
>
> Since one product can be in more then one category, is it correct to
> create thirs table with those info?
>
> CREATE TABLE ac_products_categories (
>   pc_id INTEGER(6) UNSIGNED NOT NULL AUTO_INCREMENT,
>   ac_products_product_id INTEGER(8) UNSIGNED NOT NULL,
>   p_id INTEGER(6) UNSIGNED NULL,
>   c_id INTEGER(6) UNSIGNED NULL,
>   PRIMARY KEY(pc_id),
>   INDEX pc_index(p_id, c_id),
> );
>
> Or, there is better solution?
>
> Thanks!
>
> -afan
>
When there is a many-to-many relationship between two tables, the
traditional solution is to create a table, usually called an "association"
table (or an "intersection" table) to show how they are related. Typically,
the association table contains the primary key from each of the original
tables. The primary keys from the two original tables, are, of course,
foreign keys in the new table that point back to the original tables. The
primary key of the new table is the *combination* of the primary keys of the
original tables.

For example, let's imagine that we have Projects and Employees and that any
given project can have many employees on it and that any given employee can
be working on multiple projects. That is a true many-to-many relationship.
The appropriate way to represent this in the database would be along these
lines:

PROJECT Table [Primary key: Projno]
==========
Projno    ProjectName                    [Other columns describing
project....]
-------    ---------------
A           New Marketing System
B           Payroll System Revisions
C           New Inventory System

EMPLOYEE Table [Primary key: Empno]
===========
Empno    Lastname    [Other columns describing employee....]
--------    -----------
1            Jones
2            Brown
3            Smith
4            Malone

PROJECT_EMPLOYEE Table [Primary key: Projno + Empno; Foreign key #1: Projno;
Foreign key #2: Empno]
=================
Projno    Empno    [Other columns describing *combination* of project and
employee....]
-------    --------
A            1
A            3
B            2
C            2
C            3

In other words, Project A is staffed by employees 1 and 3; Project B is
staffed by employee 2 alone; Project C is staffed by employees 2 and 3.
(Employee 4 doesn't have a project right now; maybe she is on maternity
leave?].

In a properly designed table, the only other columns that should be in the
association table are ones that have some connection to BOTH the Project and
the Employee. For instance, if you wanted to indicate the percentage of the
employee's week that should be dedicated to each project, you could add a
column for that:

Projno    Empno    Time_Pct_Per_Week
-------    --------     -----------------------
A            1            40
A            3            60
B            2            80
C            2            20
C            3            40

In other words: employee 1 spends 40% of his time each week on Project A;
employee 2 spends 80% of her time on Project B and 20% on Project C; and
employee 3 spends 60% of his time on Project A and 40% on Project C. This
percentage should NOT be in the Project or in the Employee tables because it
refers to the COMBINATION of Project and Employee; its only proper place is
in the association table.

In your particular situation, the only columns you really need in your
association table are product_id and cat_id. The other columns are
unnecessary; in fact, it's probably a bad idea to have them there at all.
Also, the primary key in the association table must be BOTH product_id and
cat_id, not just the product_id. I'm a big user of Referential Integrity so
I'd also define product_id and cat_id as two separate foreign keys in the
association table; this will necessitate using InnoDB as your storage engine
but it will ensure that your association table only ever contains valid
data, which is very useful in my opinion. However, if you can't use InnoDB
for some reason, you could omit the foreign key definitions as they are only
enforced in InnoDB (unless this has changed recently).

Sorry for the long reply but I needed to explain how things are properly
done before I told you what was wrong with your design and how to make it
better.

Rhino



-- 
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.344 / Virus Database: 267.12.0/134 - Release Date: 14/10/2005

Thread
one product in more categoriesafan@afan.net18 Oct
  • Re: one product in more categoriesSGreen18 Oct
    • Re: one product in more categoriesafan@afan.net18 Oct
    • Re: one product in more categoriesafan@afan.net18 Oct
      • Re: one product in more categoriesPeter Brawley18 Oct
        • Re: one product in more categoriesafan@afan.net19 Oct
          • Re: one product in more categoriesafan@afan.net19 Oct
          • Re: one product in more categoriesPeter Brawley19 Oct
          • Re: one product in more categoriesSGreen19 Oct
            • Re: one product in more categoriesafan@afan.net20 Oct
              • Re: one product in more categoriesPeter Brawley20 Oct
                • Re: one product in more categoriesafan@afan.net20 Oct
                  • Re: one product in more categoriesPeter Brawley21 Oct
              • Re: one product in more categoriesSGreen21 Oct
                • Re: one product in more categoriesafan@afan.net21 Oct
                  • Re: one product in more categoriesSGreen21 Oct
    • Re: one product in more categoriesRhino18 Oct
  • Re: one product in more categoriesRhino18 Oct
    • case insensitive searchafan@afan.net19 Nov
      • Re: case insensitive searchJasper Bryant-Greene19 Nov
        • Re: case insensitive searchafan@afan.net20 Nov
          • Re: case insensitive searchBjörn Persson21 Nov
      • Re: case insensitive searchScott Haneda19 Nov
      • Re: case insensitive searchBjörn Persson19 Nov