let me count the ways

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

hey all,
let's say i have some categories that i want to attach to a product. my
thought on storing this information was storing each of the categories for a
particular product into one database field, semicolon delimited?

is there a another way to do this? if the above option is desired what would
be the best way to separate the categories out when reading the product from
the database?

thanks,
rodchar
 
hey all,
let's say i have some categories that i want to attach to a product. my
thought on storing this information was storing each of the categories for
a
particular product into one database field, semicolon delimited?

is there a another way to do this? if the above option is desired what
would
be the best way to separate the categories out when reading the product
from
the database?
Click to expand...

What you need here is a classic "many-to-many" relationship e.g.

Products
---------
ProductID int
Product varchar(50)

Categories
-----------
CategoryID int
Category varchar(50)

ProductCategories
-------------------
ProductID
CategoryID

This allows you to map the relationship between products and categories. One
product can be in many categories, and one category can contain many
products...
 
hey all,
let's say i have some categories that i want to attach to a product. my
thought on storing this information was storing each of the categories for a
particular product into one database field, semicolon delimited?

is there a another way to do this? if the above option is desired what would
be the best way to separate the categories out when reading the product from
the database?

thanks,
rodchar
Click to expand...

That approach wouldn't even satisfy the conditions for 1NF. Wouldn't
it better to create a category and add a foreign key reference to it
from the product table? That way you could easily separate them out
when quering. Do you have a specific reason for choosing a
denormalized structure?
 
That approach wouldn't even satisfy the conditions for 1NF. Wouldn't
it better to create a category and add a foreign key reference to it
from the product table? That way you could easily separate them out
when quering. Do you have a specific reason for choosing a
denormalized structure?
Click to expand...

Err...you'd need to create a category table *and* a table that relates
products to categories in a many-to-many relationship.
 
from using sql database search aspect, one single field for multiple
value(attributes) is bad


Suppose you want select product by categories, with delimited field holding
multiple categories the search would be brutally slow and expensive and
definitely not scalable.
you should have association table

productID category
and you can query the joint tables very easily and quickly with simple sql

select p.product , Category from Productstable as p, CategoryTable as c
where p.productID = c.productID and category ="somecategory"
 
Back
Top