Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2004
    Posts
    5

    Question category,nested subcategory, how to ?

    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.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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 )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2004
    Posts
    5
    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

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2004
    Posts
    5
    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.

  6. #6
    Join Date
    Nov 2004
    Posts
    5
    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..

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Nov 2004
    Posts
    5
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •