let me count the ways

[=?Utf-8?B?cm9kY2hhcg==?=]

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

[Brian Gideon]

On Aug 8, 3:34 pm, rodchar <[e-mail]:

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

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?

[Mark Rae [MVP]]

"rodchar" <ro*****@discussions.microsoft.comwrote in message
news:C7**********************************@microsof t.com...

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?

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...
--
Mark Rae
ASP.NET MVP
http://www.markrae.net

[Brian Gideon]

On Aug 8, 3:53 pm, Brian Gideon <[e-mail]:

On Aug 8, 3:34 pm, rodchar <[e-mail]:

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

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?

Err...you'd need to create a category table *and* a table that relates
products to categories in a many-to-many relationship.

[GS]

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"
"rodchar" <ro*****@discussions.microsoft.comwrote in message
news:C7**********************************@microsof t.com...

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

[=?Utf-8?B?cm9kY2hhcg==?=]

Thanks everyone for sharing this generous knowledge.
Rod.

"rodchar" wrote:

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