Ron Peterson wrote:
CREATE TABLE category_edge (
parent INTEGER
NOT NULL
REFERENCES category_node(id),
child INTEGER
NOT NULL
REFERENCES category_node(id)
);
Just for the sake of anal-retentive
Stuart Statman wrote:
I would suggest, instead, to create a table that represents your hierarchy
without adding columns. For example :
create table Category (
CategoryID int4 not null primary key,
ParentCategoryID int4 not null REFERENCES Category (CategoryID),
CategoryName
On Thu, 14 Dec 2000, Tulassay Zsolt wrote:
You can find the article dealing with this at
http://www.utdt.edu/~mig/sql-trees
sorry i pasted in the wrong url (this was mentioned in an earlier post)
the correct one is:
A look at SQL Trees (by Joe Celko)
http://www.dbmsmag.com/9603d06.html
There actually is a model of tree structures in SQL databases which is
different from those mentioned earlier in that it represents the tree
as nested sets (ie. nodes are subsets of parent sets (parent nodes)).
There is a huge advantage in this model as it eliminates the need for
recursion. For
somebody already showed table structure, but i'll ad some more code to this:
table:
CREATE TABLE groups (
id INT4 NOT NULL DEFAULT NEXTVAL('groups_seq'),
parent_idINT4 NOT NULL DEFAULT 0,
name TEXT NOT NULL DEFAULT '',
active BOOL NOT NULL
I am just thinking about the data model for a little content management system that I
am
currently planning. Individual articles are sorted under different categories which
branch
into subcategories, sub-subcategories etc. up to a depth of about 6 or 7 levels. The
structure should be
Frank,
Please look in the list archives. About 2 months ago this topic came
up and was discussed extensively (including a creative solution by yours
truly).
-Josh Berkus
--
__AGLIO DATABASE SOLUTIONS___
On Wed, Dec 13, 2000 at 04:48:47PM +0100, Frank Joerdens allegedly wrote:
I am just thinking about the data model for a little content management system that
I am
currently planning. Individual articles are sorted under different categories which
branch
into subcategories, sub-subcategories
On Wed, Dec 13, 2000 at 11:38:18AM -0800, Stuart Statman wrote:
[ . . . ]
I would suggest, instead, to create a table that represents your hierarchy
without adding columns. For example :
create table Category (
CategoryID int4 not null primary key,
ParentCategoryID int4 not null
On Wed, Dec 13, 2000 at 11:04:13AM -0800, Josh Berkus wrote:
Frank,
Please look in the list archives. About 2 months ago this topic came
up and was discussed extensively (including a creative solution by yours
truly).
Hm, neither my archives nor a search on the postgresql.org page
Frank Joerdens wrote:
On Wed, Dec 13, 2000 at 11:04:13AM -0800, Josh Berkus wrote:
Frank,
Please look in the list archives. About 2 months ago this topic came
up and was discussed extensively (including a creative solution by yours
truly).
Hm, neither my archives nor a
What I am thinking now is that you would keep the index
in a separate index table linked with the primary
key in the articles table), which would have 6 or 7 fields
initially, and that you'd add columns with the alter table
command, if need be, to make the structure deeper.
I would suggest,
Frank, etc:
create table Category (
CategoryID int4 not null primary key,
ParentCategoryID int4 not null REFERENCES Category (CategoryID),
CategoryName varchar(100)
);
That was it. I also gave an example of a UNION query that would display
the whole category tree in
On Wed, Dec 13, 2000 at 12:09:06PM -0800, Stuart Statman allegedly wrote:
The way I'd do it if I had to do it again:
Give each record a unique id, generated by the application.
Denote levels with extra letters.
So:
AA - Automotive transport
- Cars
AAAB -
On Wed, Dec 13, 2000 at 04:49:51PM -0800, Josh Berkus allegedly wrote:
Stuart,
I don't think I'd be comfortable with having the node_level column in the
table structure. First, because you can derive that value using a function,
it's duplicate data. Second, if you decide to take an
Don't if this will help but there is a really good book that discuss this problem in
details.
The book is called "SQL for Smarties" by Joe Celko. It covers lots of advance topics
(tree being one of them). Very good book. Check out on Amazon:
Mathijs Brands wrote:
On Wed, Dec 13, 2000 at 04:49:51PM -0800, Josh Berkus allegedly wrote:
Stuart,
I don't think I'd be comfortable with having the node_level column in the
table structure. First, because you can derive that value using a function,
it's duplicate data. Second,
On Wednesday 13 December 2000 18:05, Josh Berkus wrote:
Frank, etc:
create table Category (
CategoryID int4 not null primary key,
ParentCategoryID int4 not null REFERENCES Category (CategoryID),
CategoryName varchar(100)
);
I made a message board with a hierarchy
18 matches
Mail list logo
