Input form for creating hierarchy structure

M

Marco Alting

Hi

I'm trying to create an input screen that allows users to create a
hierarchical structure as the one below, but have no clue where to start or
how to construct it. Can anyone help me out here?

There must be a way to let users create something like this using an input
form:

Level Description Price
C0.00 Car <will be the sum of all
sublevels>
C0.00.01 Steering 2000
C0.00.02 wheels 500
C0.00.03 horn 50
C0.00.03.01 hornsound 5
C0.00.04 wipers 100
C0.01 Truck <will be the sum of all sublevels>
C0.01.01 Steering 2000
C0.01.02 mirrors 500
C0.01.03 load 50
C0.01.03.01 holders 5
C0.00.04 wipers 100

etc.

I would like to create a system that allows many sublevels, that are
predefined, in an admin section. And it is preferred to have the level-codes
created automatically.
 
A

Alan

Is this a DB design or UI qstn? If both, then one way is to use a table,
called Components or something, with an FK column (called ParentComponentID)
that stores the ID of the parent assembly. Create a self-join on this column
to the table's PK column, ComponentID.

When adding a new component you'll have to select the parent component in
some way - one simple implementation is to just use a DDL, but this depend
on your app. At this point you'll also enter the description and whatever
other attributes are necessary.

When doing the insert in your form processor you can derive the Level
identifier (which is numeric) by doing a 'SELECT MAX (Level) +1 FROM
Components WHERE ParentComponentID = Request.Form("ParentComponentID")' or
similar - this will give you the next integer in the series at that level in
the hierarchy. Just store that value with this new Component.

The only issues you'll have regard recursion. Put some thought into how your
app is going to be used - to derive the Price and Level of a component (for
display for example) you'll have to recurse through all levels above it in
the hierarchy either concatenating in the case of the Level, or summing in
the case of Price, to get the values you need. You can optimise this to a
degree by using a breadth-first-search and storing the intermediate values
as you parse your tree, but a better option (probably) is to derive these
values for each component as it is inserted and write some pretty robust
code to keep these values correct as components are removed and/or
re-parented from/within your tree.

Here are a couple of article to get you started:
http://www.sqlteam.com/item.asp?ItemID=8866
http://www.intelligententerprise.com/001020/celko1_1.shtml

Good luck. Let us know how it goes.

Alan
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,483
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top