MY SQL or ACCESS 2000

[rrrrr]

Can somebody help me with slef relationships in MY sql or MS Access 2000.
I want to set up a one to many relationship in the same table in which the primary key is .
Example
Means I want one category which has manysub categories and then many categories have many products.
so it would be
Category
|
|
|
Category1
|
|
|
Category2
|
|
|
Category(N)
|
|
|
PRODUCTS
Now i just have two tables category and produucts..
I want to set up a self relationship a one to many relationship between category and many categories and each category may have many products...
Can any one please help me with this ...
Thank You ,
Email me at [e-mail]
--Tim....

[stephanie]

I'm not sure I understand exactly what you need, but I would use something like that (w/ MySQL):

<?

CREATE TABLE product (
product_ID int(9) NOT NULL auto_increment,
category_id int(3) unsigned NOT NULL,
//etc...
PRIMARY KEY product_ID
);

CREATE TABLE category (
category_id int(3) unsigned NOT NULL,
category_name varchar(16) NOT NULL,
PRIMARY KEY category_id
);

CREATE TABLE sub_category (
sub_category_id int(3) unsigned NOT NULL,
sub_category_name varchar(16) NOT NULL,
category_id int(3) unsigned NOT NULL,
PRIMARY KEY sub_category_id
);

?>

In this case, each product has one category
each category can have many products
& each category can have many subcategories

Hope this helps you.