building categories table

[angiras]

I am building a table (access 2000 or SQL 2000) with categories and products, what should be the best structure `
the products will be classified in any of the 3 class

---------------------------------------------

Category_1 :
id_Category_1 (numero auto)
name (text)

Category_2 :
id_Category_2 (numero auto)
id_Category_1 (numero auto of Category_1)
name (text)

Category_3 :
id_Category_3 (numero auto)
id_Category_2 (numero auto of Category_2)
name (text)

Product :
id_Product (numero auto)
id_Category_1 (numero auto of Category_1)
id_Category_2 (numero auto of Category_2)
id_Category_3 (numero auto of Category_3)
namet (text)

---------------------------------------------

or

---------------------------------------------

Categories :
id_Categories (numero auto)
id_CategorySup (id of the Category superior)
Level (numeric : 1 or 2 or 3)
name (text)

Product :
id_Product (numero auto)
id_Categories (numero auto of Categories)
name (text)

---------------------------------------------

what is the best method , or another one !?
(with the second method it's easier to have as many level of categories as wished and does not affect the table products)

thank you

[skhanal]

I prefer the second option as it is more flexible in case you need to add a category in future.

Only problem is, there is no native command in sql server to list all the hierarchy (unlike Oracle which has CONNECT BY clause in SELECT). But you can write a stored procedure to that and there is an example in books online.

[angiras]

thank you