----- Original Message ----- From: <[EMAIL PROTECTED]> To: <mysql@lists.mysql.com> 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
