|
|
Posted by Steve on 10/07/05 20:12
> Is it possible to fill a field with multiple values ? I am cataloguing
> products that can fall into multiple subcategories (all of which are
> referenced by an id key to another table containing the subcat titles,
> i.e. 0=vintage, 1=mens, 2=womens etc.)
>
> What I really want is an enum type field that I can fill with more than
> one value. I would use seperate fields (subcat1, subcat2 etc.) but the
> field, presently called id_subcategory is special in that when my
> script encounters 'id_' it uses the following characters
> ('subcategory') as the name of the table containing the values for the
> html select form. Therefore I would need to have several separate but
> identical tables to pull the subcategories from. Not impossible, but
> annoying the have to update five tables each time a new category is
> added.
You could do this, by (for instance) making your field a varchar type
and using commas or some other character to separate the multiple
values. This is a bad way of doing it though, for many reasons, not
least of which is: how long do you make the varchar? The enum method is
also possible but is not as flexible as the industry standard method of
dealing with this.
The process you should go through is called "normalisation", and if you
google for "third normal form" or check wikipedia you will find a lot
of useful information.
In this case, you have a common problem with a common solution.
* table product - just describe the product, no category information
* table category - just describe the categories, no other data
* table product-category - describe what product is in what category
Having split your data across these tables, you can now put a product
into a new category not by altering the products table but by adding a
new row to the products-categories table.
This is a flexible way to manage your data. You can easily: find what
products are in a particular category; find what categories a product
is in; count products by category; add, remove, rename product
categories; etc etc. To make these queries you need to do joins between
the tables.
An example...
TABLE product
id, description, cost, ...
TABLE category
id, description, ...
TABLE `product-category`
product_id, category_id
Find the categories for product "RACER":
SELECT category.description
FROM product
JOIN `product-category`
ON product.id = `product-category`.product_id
JOIN category
ON `product-category`.category_id = category.id
WHERE product.description = "RACER"
Looks complicated but in English it just says 'find me all the category
records that are listed by id in the product-category table where the
corresponding product id is the id for "RACER"'.
---
Steve
Navigation:
[Reply to this message]
|