What is the most efficiant way to handle multiple data relations?

Hey,
I'm writing a new website that is based on MS SQL 2005.
I have a categories table that holds 2 fields: ID (auto number, key) to identify the category, and a field that holds the category name.

The second table I have is the products table. each row in the products table is a single product. each product can be associated with more than one category.

what is the best method to work with:
1. create a field in the products table to told a delimited string with all the categories ID's that the product belongs to. and when entering a category in the website selecting using CHARINDEX function to check if the category ID is within that field.

OR

2. create a third table that has 2 fields: the first field is identifier of the category and the second field is the identifier of the product.
each product will have multiple records on that table for each category it belongs to. and the select will probebly be used with a nested select in it to make the selection.

which of the two ways is better and more officiant and faster for use?

Thanks

 

Number 2 is far more efficient.

Use the database for what it was designed to do and thats keep relationships. CHARINDEX is very slow on large datasets.

 

Ok. thanks

to make it even better - should I set indexes/keys? if so, how and on what?

 

yes in a database you should ALWAYS set primary keys and make indexes on columns you join on

to add a FK:
ALTER TABLE [dbo].[table]
ADD CONSTRAINT [fk_name]
FOREIGN KEY (column in table)
REFERENCES dbo.table2 (column in table2)

set FK's on all the indexes that are related to the PK in another column
so in your third table you'll have a FK to your category and one to your products table

 

I'm new to the databases optimizations, so bare with me.... I'll describe what I did - tell me if that's correct and what exactly I should do.

I have 3 tables now:
1. table name: products
the table has a field named ID of type int (auto increment) and that's set as Primary key.
the table has more fields to describe different product characteristics (like name, description, price....)

2. table name: categories
the table has a field named ID of type int (auto increment) and that's set as Primary key.
the table has one more field called categoryName that holds the name.

3. table name: product2category
the table has an int field that holds the productID and another int field to hold categoryID.

is that ok? how should I create the index?

Thanks

 

to make a clustered index:

CREATE CLUSTERED INDEX [idx_prod2cat]
ON [dbo].[product2category]
(
[productid] ASC,
[categoryid] ASC
);

to make FKs:

ALTER TABLE [dbo].[product2category]
ADD CONSTRAINT [fk_prodcat_prod]
FOREIGN KEY (productid)
REFERENCES dbo.products (productid)

ALTER TABLE [dbo].[product2category]
ADD CONSTRAINT [fk_prodcat_cat]
FOREIGN KEY (categoryid)
REFERENCES dbo.category (categoryid)



to clustered index will make searches for a specific product very fast, and the fk's are for your referential integrity so you cannot insert a value that does not exist in one of the other tables (or you can't delete a value that's used)

 

2 option is best and you don't have to create an index because 3rd table is based on many-to-many relationship.