Hello.
I am new to database design. i am using access to creat database structure for one of my projects.
I like to know, how i can implement category as well as subcategories ( nested ) in my database.?

Thanks in advance.

create table categories
( id integer not null constraint cat_pk primary key
. name text(50) not null
, parent_id integer null
, constraint parent_fk foreign key (parent_id) references categories (id)
)

insert into categories values ( 100, 'fruit', null )
insert into categories values ( 101, 'apple', 100 )
insert into categories values ( 102, 'mango', 100 )
insert into categories values ( 103, 'mutsu', 101 )
insert into categories values ( 200, 'music', null )
insert into categories values ( 201, 'opera', 200 )
insert into categories values ( 202, 'blues', 200 )

Thanks for your reply..
I kinda don't undrestand the statment you just mentioned. is it possible to use access like view to to explain this process.
like
category table
product name : text
product id : null ?? ( pk ) right ?

i am totaly confused. sorry, iam way too slow to catch on stuff like this

Thanks a million

open access, select query > New > SQL View

paste the create statement, and execute it

then paste the insert statements, one at a time, and execute them

then open the table

thanks for the info.. althogh i keep getting error message when i run sql view, but i am i pretty sure is my own fault.. I will try to sort things out.anyways. thanks for putting me in the right path.

best regards.

thanks , finally i made it work. table seems to be very easy, but humm. i have no idea how this table works the way it dose.
it seems parent_id = fk that refrences to ID = pk .. now if i can only learn what is going on here. would be great

again thanks for the table structure..

what is going on here? i thought my examples did a pretty good job

it's a self-referencing table

every category references its parent category, except the categories at the top of their hierarchies

the examples i gave you can be illustrated as follows:

CAT -- fruit
SUBCAT -- apple
SUBSUBCAT -- mutsu
SUBCAT -- mango
CAT -- music
SUBCAT -- opera
SUBCAT -- blues

I got it working ..since i have no idea about constraint , i had to google around to find some info on it.
will take months to undrestand it ,but is better then pulling my hair.
i just also figured how to make same table through the use of access releationship.
Thanks.