Difficult Design Concept

[T2T2]

I am about to start a new project which involves marketing information.

The system data will capture customer product interests.
One difficulty is that the data will be grouped under varying headings and sub headings , so I will need a data structure that will allow varying break ups of products.

For example under Boats I have 2 sub headings, then one sub heading goes into another level of subheadings.

Example 1
---------------------------------------
First Level - Boat
Second Level - Manufacturer
Third Level - Motor Power

Example 2
----------------------------------------
First Level - Boat
Second Level - Size

This means that on one path I have manufacturer data and its sub heading data showing the different motor options available.
On the other path, under boat, I am only showing size information.

I will have many products broken down into anything up to 4 sub heading of information.

I believe a table that contains columns showing heading and subheading is required, were a join to itself will be used to build the top down relationships.
In other words the data within one table should capture data and point to the upper heading level which is another row in the same table.

I have never done this with VB , maybe someone has experience here and can guide me as to the best approach under vb?

Sorry if my description is vague, its the best explanation I can think of for the time being.

Thanks

[jp140768]

I don't know if this is of relevance, but I think I did something similar in a questions and answers type database. You are allowed to categorise your questions, then sub categorise and further subcategorise.

EG one on the main categories, could be batch run, under this you have all the jobs that run in the batch, and for each job you may have different questions eg how to rerun etc.

What I did was set up three tables:
1st Category,
2nd Category and,
3rd Category.

All tables have an counter as a primary key. The second and third table, have another index which contains the counter on the previous table.

This of course means that if someone wanted to know how to rerun multiple jobs, they may have to setup the third category to have multiple entries of RERUN.

On the screen, I have three combo boxes, the first list populated and the other two empty. The click event on each combo box, causes the next combo box to be populated via an SQL statement

eg

select * from cat2 where fkey = combo.itemdata(combo.listindex).

When the user updates their question, the three values are written to the questions table.

Probably not the best solution, but it seems to work.

[mikedershowitz]

I suggest being a little more dynamic with the solution.

Put all categories in the same table.

Have second table exclusively for drawing relationships between categories and their subcategories. Every row in this table is a relationship between a cat and a subcat.

When you build your interface, query the second, relationship table, each row being a drop-down, created in sequence.

If you need to add a sequence, add a sequence counter to the relationship table as a way to draw preference.

Hope this helps!

[T2T2]

Thanks JP & Mike, I appreciate the alternative approaches.


I think dynamic is essential, I need to establish a system that won't need re work every time a new sub level of information is required.

After giving it some thought I will probably set up a table which contains a row of information and a pointer to the parent category it belongs to.

For example the columns would be something like


Item
Description
Parent Heading


So I expect to have the following rows in my table representing 3 levels of heading and detail


Item : Boats
Description : "Boating Products"
Parent Heading: Null

Item : Manufacturer Yamaha
Description : "Yamaha Boat model abc"
Parent Heading: Boats

Item : Engine Power 200 horse power
Description : Engine Model 200hp
Parent Heading : Manufacture Yamaha


Both the Item and Parent Heading fields are the same attributes and the parent is a link to the item column in another row.
Hence the nulll value at the top Item "Boats", which has no parent.

This will allow any number of sub levels to report on.

I have used this method on a number of other projects, but never with VB6 I would imagine SQL can handle this on the database side but VB I need to think about RecordSets and how to use them?

Being a relative newbee to VB I feel this will be a challenging project??

[aio]

Design your db according to the usual norms. Then, one column of the item table is a code that points to its characteristic in this mother-child relationship table.

MOTHER	CHILD		
------  -----			
BOAT	BOAT	    <-- Same Mother-child Entry indicates top level
BOAT	SIZE
BOAT	BUILDER	    <--- Builder has mother(boat) & also a mother of Motor, 
BUILDER	MOTOR		 so it's somewhere in the middle 
BUILDER	LOCATION
MOTOR	POWER
MOTOR	PROFELLER
MOTOR	PARTS	    <-- Power, Profeller, Parts doesn't have child, so they are lowest

The resulting hierarchy:

                      BOAT   
     +-----------------+---------------+ 
  SIZE                             BUILDER 
                                +-----+---------+ 
                              MOTOR         LOCATION 
                      +--------+----------+
                   POWER    PROFELLER  PARTS

Should you need more explanations, don't hesitate to ask here.

[T2T2]

Thanks Aio,


if you have code that uses this table relationship I would like to view it?

I am sure there is more than one way to approach this solution.

[cjard]

Originally posted by T2T
Thanks Aio,


if you have code that uses this table relationship

table?

aio drew a tree!?

[George1111]

The beauty of Mother Child relationships is that you can have limitless up connections - problem is that when you are reporting you need to drill down

The example shown with the "boat" mother child is neat but will require a truck load of preprocessing to achieve a report.

Furthermore, if one of the levels gets corrupted (or lost) then you have a child (or children) dynamically orphaned.
ie, stranded in never never land without a way back to the mother.

If you replicate the Motherlinks to records below, then at least you will not have to start at the bottom every time you want to make a report. But then, you have just about gone back to the traditional approach by doing this.


The more traditional approach, which is also very simple to program without getting lost in the relationships, is to firstly try to identify the number of levels you need - say 6

then your record lay out becomes

CODE 32145666 (PART CODE)
L1 BOAT B1A
L2 MANUFACTURER YAMAHA
L3 SIZE 16
L4 MOTOR
L5 PART
L6
DESCRIPTION
DATA1
DATA2
ETC


Then all you need is a table with descriptors of all the possible values of each of the 6 levels

[Cambece]

When we have this type of design problem we tackle it a little different.

We make an alternate key on what you are calling the parent table of type guid. You know one of those huge honking number
{23232-2323-232323-2323}

So the table would look like this. For the sake of example I am going to deal in widgets


WidgetMaster
Name varchar(10)
Type varchar(10)
pruuid guid


Then we create another table called WidgetChild

WidgetChild
ParentPruuid guid
ChildPruuid guid


Then if could create your relationship like so.

WidgetMaster

Record 1
Boat
Big
xxx

Record 2
Motor
Small
yyy

Then your widget child table would do the linking

WidgetChild

Record 1
xxx
xxx

Record 2
xxx
yyy

Does this make any sense?

Regards,
David Ritchie

CodeLab Technology Group