Brainstorming: Ideas for categories database structure please

Hi

Thanks in advance for any ideas.

What I'm trying to do, is have a category system like Yahoo or these
newsgroups where you have a parent "COMP" and multiple children "LANG"
or "DATABASES". However, these can also have children. So for
"DATABASES", you have "MS-SQLSERVER" "ACCESS" "ORACLE8" etc

My question is, what is the best way to store these in a database and
also allow me to retrieve the data

I'm not sure how to procede

Thanks

SamWhat you want to do is store a tree structure. Celko has written
extensively on Nested Sets - you can also use a modified adjacency list
(something I prefer), storing the full path to each node with each record.
Anyway, if you search on Nested Sets or Adjacency List, you will find all
the information you need.

"Samuel Hon" <noreply@.samuelhon.co.uk> wrote in message
news:c8672b7d.0309231103.21d812fb@.posting.google.c om...
> Hi
> Thanks in advance for any ideas.
> What I'm trying to do, is have a category system like Yahoo or these
> newsgroups where you have a parent "COMP" and multiple children "LANG"
> or "DATABASES". However, these can also have children. So for
> "DATABASES", you have "MS-SQLSERVER" "ACCESS" "ORACLE8" etc
> My question is, what is the best way to store these in a database and
> also allow me to retrieve the data
> I'm not sure how to procede
> Thanks
> Sam|||Thanks

"Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in message news:<bkq745$9fp$1$8300dec7@.news.demon.co.uk>...
> What you want to do is store a tree structure. Celko has written
> extensively on Nested Sets - you can also use a modified adjacency list
> (something I prefer), storing the full path to each node with each record.
> Anyway, if you search on Nested Sets or Adjacency List, you will find all
> the information you need.
> "Samuel Hon" <noreply@.samuelhon.co.uk> wrote in message
> news:c8672b7d.0309231103.21d812fb@.posting.google.c om...
> > Hi
> > Thanks in advance for any ideas.
> > What I'm trying to do, is have a category system like Yahoo or these
> > newsgroups where you have a parent "COMP" and multiple children "LANG"
> > or "DATABASES". However, these can also have children. So for
> > "DATABASES", you have "MS-SQLSERVER" "ACCESS" "ORACLE8" etc
> > My question is, what is the best way to store these in a database and
> > also allow me to retrieve the data
> > I'm not sure how to procede
> > Thanks
> > Sam|||You can do it in one table.

Use a few fields like child-id (unique index), name, parent_id

so your table might look something like

CHILD_ID NAME PARENT_ID
1 COMP 0
2 LANG 1
3 DATABASES 1
4 MS_SQLSERVER 3
5 ACCESS 3
6 ORACLE 3
7 VB 5 2
8 COBOL 2
9 ORACLE FORMS 2
10 ASP 2
11 HTML 2
12 JSCRIPT 2

So you can immediately , by knowing the parent ID, which would be a hard
coded static reference code (so languages will always have a parent code of
2), find all child codes and therefore types

???????

"Samuel Hon" <noreply@.samuelhon.co.uk> wrote in message
news:c8672b7d.0309240054.14d3f324@.posting.google.c om...
> Thanks
> "Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in
message news:<bkq745$9fp$1$8300dec7@.news.demon.co.uk>...
> > What you want to do is store a tree structure. Celko has written
> > extensively on Nested Sets - you can also use a modified adjacency list
> > (something I prefer), storing the full path to each node with each
record.
> > Anyway, if you search on Nested Sets or Adjacency List, you will find
all
> > the information you need.
> > "Samuel Hon" <noreply@.samuelhon.co.uk> wrote in message
> > news:c8672b7d.0309231103.21d812fb@.posting.google.c om...
> > > Hi
> > > > Thanks in advance for any ideas.
> > > > What I'm trying to do, is have a category system like Yahoo or these
> > > newsgroups where you have a parent "COMP" and multiple children "LANG"
> > > or "DATABASES". However, these can also have children. So for
> > > "DATABASES", you have "MS-SQLSERVER" "ACCESS" "ORACLE8" etc
> > > > My question is, what is the best way to store these in a database and
> > > also allow me to retrieve the data
> > > > I'm not sure how to procede
> > > > Thanks
> > > > Sam|||You have here an adjacency list. Pretty standard way of doing things BUT
very difficult to write queries about subtrees and relations that aren't
immediate parent/child. You might also store the full path with each
record, as I do (which is usually efficient enough) to allow easy subtree
queries using "LIKE" operator.

"cor_blimey" <no_spam@.no_spam.com> wrote in message
news:1Jdcb.523$_N1.384673@.newsfep1-win.server.ntli.net...
> You can do it in one table.
> Use a few fields like child-id (unique index), name, parent_id
> so your table might look something like
> CHILD_ID NAME PARENT_ID
> 1 COMP 0
> 2 LANG 1
> 3 DATABASES 1
> 4 MS_SQLSERVER 3
> 5 ACCESS 3
> 6 ORACLE 3
> 7 VB 5 2
> 8 COBOL 2
> 9 ORACLE FORMS 2
> 10 ASP 2
> 11 HTML 2
> 12 JSCRIPT 2
>
> So you can immediately , by knowing the parent ID, which would be a hard
> coded static reference code (so languages will always have a parent code
of
> 2), find all child codes and therefore types
> ???????
>
> "Samuel Hon" <noreply@.samuelhon.co.uk> wrote in message
> news:c8672b7d.0309240054.14d3f324@.posting.google.c om...
> > Thanks
> > "Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in
> message news:<bkq745$9fp$1$8300dec7@.news.demon.co.uk>...
> > > What you want to do is store a tree structure. Celko has written
> > > extensively on Nested Sets - you can also use a modified adjacency
list
> > > (something I prefer), storing the full path to each node with each
> record.
> > > Anyway, if you search on Nested Sets or Adjacency List, you will find
> all
> > > the information you need.
> > > > "Samuel Hon" <noreply@.samuelhon.co.uk> wrote in message
> > > news:c8672b7d.0309231103.21d812fb@.posting.google.c om...
> > > > Hi
> > > > > > Thanks in advance for any ideas.
> > > > > > What I'm trying to do, is have a category system like Yahoo or these
> > > > newsgroups where you have a parent "COMP" and multiple children
"LANG"
> > > > or "DATABASES". However, these can also have children. So for
> > > > "DATABASES", you have "MS-SQLSERVER" "ACCESS" "ORACLE8" etc
> > > > > > My question is, what is the best way to store these in a database
and
> > > > also allow me to retrieve the data
> > > > > > I'm not sure how to procede
> > > > > > Thanks
> > > > > > Sam