473,322 Members | 1,379 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

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

Sam
Jul 20 '05 #1
4 3912
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" <no*****@samuelhon.co.uk> wrote in message
news:c8**************************@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

Jul 20 '05 #2
Thanks

"Robin Tucker" <id*************************@reallyidont.com> wrote in message news:<bk*******************@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" <no*****@samuelhon.co.uk> wrote in message
news:c8**************************@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

Jul 20 '05 #3
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" <no*****@samuelhon.co.uk> wrote in message
news:c8**************************@posting.google.c om...
Thanks

"Robin Tucker" <id*************************@reallyidont.com> wrote in

message news:<bk*******************@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" <no*****@samuelhon.co.uk> wrote in message
news:c8**************************@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

Jul 20 '05 #4
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:1J******************@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" <no*****@samuelhon.co.uk> wrote in message
news:c8**************************@posting.google.c om...
Thanks

"Robin Tucker" <id*************************@reallyidont.com> wrote in

message news:<bk*******************@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" <no*****@samuelhon.co.uk> wrote in message
news:c8**************************@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


Jul 20 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: Brian | last post by:
Greetings, I'm working on a project that involved that has need of a categorization system. Logically speaking, the system will have elements, these elements will belong to at least a single...
0
by: Gennadiy Tit | last post by:
I have to load 1700 records from DB into tree view (NOT LIST VIEW). I have tried to load it with locking the windows and control and other stuff, but it not givingme the result that I need. It...
0
by: Ralph Guzman | last post by:
TASK: I have to generate a report with all categories, subcategories and products in database. PROBLEM: I want to write one query that will return: 1. category 2. subcategory: determined by...
1
by: Neil McGuigan | last post by:
Hi, I want to store product categories in my db and am a little lost as to where to start. They can be hierarchical, such as "Books" > "Cook Books", so my table is like this: int...
24
by: Phil Latio | last post by:
Let's say I have a simple web application running with just two MySQL tables. The tables structure is as follows: Table: category category_id (PK) category_name parent_category (FK)...
2
by: tagnum | last post by:
I have written a program for unlimited categories depth, which is in use for few ecommerce websites. Like many others, I used one SQL table to store the category relation (or the tree structure)....
3
by: dkultasev | last post by:
Hello, I have database, which contains the list of laptop companies and some information about them. Some laptop models, have series, sub series, family's and so on. for example. Acer Aspire...
1
by: Wazza | last post by:
G'Day, I have a re-occurring problem and wish to design a framework or pattern to address it. I have some Ideas but I would like to do some brainstorming with my peers before commencing. The...
1
dlite922
by: dlite922 | last post by:
We've built an application that deals with business management. One of the modules is an inventory/product management for a retail store. What I want to do is create a user friendly interface...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.