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 extensible,
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-subcategor
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 n
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 pa
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 archiv
> 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 - Motorcycles
>
> The structures has the added bonus of making it very easy to
>
> 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 sugg
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 tr
[Josh Berkus]
> I've done this before for one project. Here's what you do:
>
> CREATE TABLE sample_heirarchy (
> unique_id SERIAL CONSTRAINT PRIMARY KEY,
> node_linkup INT4,
> node_level INT2,
> label VARCHAR(30)
> data
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 entire segment of your
> hierarchy and move it under another node (by changing
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
>
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 a
ASIN/1558605762/qid=976755796/sr=1-1/106-0241434-0557209
Just me $0.02
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto: Behalf Of Josh Berkus
> Sent: Thursday, December 14, 2000 10:50 AM
> To: sqllist
> Subject: Re: [SQL] How to represent a tree-str
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 d
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
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 DEFA
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.h
Ron Peterson wrote:
>
> This structure is more 'normal' in the sense that nodes without children
> (in a tree, the leaf nodes) don't have records in the edge table.
Phghpth. Should have had my coffee first. The first data structure
given would only have a null parent id for the root node, not
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-retentiv
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),
> Category
22 matches
Mail list logo
