Quantcast

Maximum PC

It is currently Sat Aug 30, 2014 1:29 pm

All times are UTC - 8 hours




Post new topic Reply to topic  [ 2 posts ] 
Author Message
 Post subject: create a tree-like structure in mysql?
PostPosted: Wed Nov 02, 2011 11:15 am 
Willamette
Willamette

Joined: Tue Feb 01, 2005 12:17 pm
Posts: 1479
Has anyone tried to create a tree-like structure in a mysql database?
I'm trying to devise a way that I can create a production selection wizard that will operate a lot like a tree where as you progress through the questions, the system will narrow down your results.

My reason for using a mysql database is so that it can be updated easily, as opposed to hard-coding the info into various script files...

I'm thinking about the idea of using a node-based system, where there is a parent node, and either 0 or more child nodes. so, as you progress through the tree, you have 0 or more options at each "level."

Suggestions or tips on how to implement this?
(I already have a generic database and some scripts as a start, but they are very basic and probably are not the most efficient methods. I'm concerned that after the tree accumulates lots of nodes, my current scheme will slow down).


Top
  Profile  
 
 Post subject: Re: create a tree-like structure in mysql?
PostPosted: Wed Nov 02, 2011 6:43 pm 
SON OF A GUN
SON OF A GUN
User avatar

Joined: Mon Nov 01, 2004 5:41 am
Posts: 11605
I have seen it implemented in one table where each row has an ID and then a ParentID field. Here is a script for MSSQL that represents a table I have in a system I support.
Code:
CREATE TABLE [dbo].[BusinessUnitTree](
   [ID] [int] IDENTITY(1,1) NOT NULL,
   [ParentID] [int] NULL,
   [Description] [varchar](100) NOT NULL,
   [Active] [bit] NOT NULL,
   [EnteredBy] [int] NOT NULL,
   [EnteredOn] [datetime] NOT NULL,
   [ChangedBy] [int] NULL,
   [ChangedOn] [datetime] NULL,
CONSTRAINT [PK_BusinessArea] PRIMARY KEY CLUSTERED
(
   [BUID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ) ON [PRIMARY]


A function to build the tree (top down):

Code:
CREATE FUNCTION [dbo].[BusinessUnit_DownTree] (@ID int)
RETURNS @t TABLE(ID int, ParentID int, [Level] int)
AS

BEGIN

WITH cte (BUID, ParentID, BUTypeID, [Level]) AS (
   SELECT
      ID,
      ParentID,
      0 as [Level]
   FROM
      BusinessUnitTree (nolock)
   WHERE
      ID = @ID
UNION ALL
   SELECT
      p.ID,
      p.ParentID,
      cte.[Level] + 1
   FROM
      cte
   JOIN
      BusinessUnitTree p (nolock) on p.ParentID = cte.ID
)

INSERT @t
SELECT
   ID,
   ParentID,
   [Level]
FROM cte

RETURN
END

GO


Top
  Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 2 posts ] 

All times are UTC - 8 hours


Who is online

Users browsing this forum: No registered users and 4 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to:  
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group