Collection Question

[WStoreyII]

I am Making a Collection for categories

There will be three fields

Category Major And Minor
For the Category i need to know what Type it is Expense/income
for the major i need to know the cat id and the major id
and for the minor the cat and the major id

at least i think that is the best way to set it up which is actually my
question
what is the best method for this a datatable which is related a collection
if so which one because i have seen a few of them

I want to be able to use this collection to populate combobox wit h there
perspective fields as wells as list boxs and also i would like to
poplulate a tree with the parent the category the child the major and the
grandchild the minor

for example if the category were groceries the major could be fruits and the
minor would be apples

i was thinking the best way of doing this would be to make a collection of
the three fields and each on have properties but do not know how do to do
this A
and b i dont know enough about collections in vb.net to decide which way
will be the most efficient

any ideas?

WStoreyII

[Cor]

Re: Collection Question

Hi WStorey

Forget the collection, you can absolutly do nothing with it as databinding
and so on.

Dim dt as new datatable(WS)
dim dc1 as new datacolumn("Cat egory")
dim dc2 as new datacolumn("Maj or")
dim dc3 as new datacolumn("Min or")
dt.columns.add( dc1)
dt.columns.add( dc2)
dt.columns.add( dc3)
dim dr as datarow = dt.newrow
dr("Category") = 1
.....
dt.add(dr)
You have now a datatablewith one row and you can add as much rows as you
wish

dt.rows(0).item (0) gives you 1 in this case.

(All is typed in here so watch typos)

I hope this helps?

Cor

[_Andy_]

Re: Collection Question

On Mon, 05 Apr 2004 01:11:06 GMT, "WStoreyII"
<papastoreyii@s bcglobal.net> wrote:
[color=blue]
>I am Making a Collection for categories
>
>There will be three fields
>
>Category Major And Minor
>For the Category i need to know what Type it is Expense/income
>for the major i need to know the cat id and the major id
>and for the minor the cat and the major id
>
>at least i think that is the best way to set it up which is actually my
>question
>what is the best method for this a datatable which is related a collection
>if so which one because i have seen a few of them
>
>I want to be able to use this collection to populate combobox wit h there
>perspective fields as wells as list boxs and also i would like to
>poplulate a tree with the parent the category the child the major and the
>grandchild the minor
>
>for example if the category were groceries the major could be fruits and the
>minor would be apples
>
>i was thinking the best way of doing this would be to make a collection of
>the three fields and each on have properties but do not know how do to do
>this A
>and b i dont know enough about collections in vb.net to decide which way
>will be the most efficient
>
>any ideas?
>
>WStoreyII
>[/color]

Getting the taxonomy right is an important part of any system design.
If I were you, I would consider implementing a standard tree structure
for your categories in a table, thus:

Table: CATEGORY
CategoryID int (not null - PRIMARY KEY)
ParentCategoryI D int (allow null)
CategoryName string
TypeCode int (1=expense,2=in come etc.)

Using your terminology: a Major category is one where the
ParentCategoryI D is null (or zero, if you prefer), all others are
Minor categories.

As you can see, you can implement a deeper tree structure at a later
date, if you need to, simply by changing the contents of the database.
A tree view would be populated simply by executing the SQL:

SELECT * FROM category ORDER BY ParentCategoryI D,CategoryName

That would guarantee that all the parent categories ("major") are read
first, and all categories appear in alphabetical order, at every tree
depth. There's no need to test to see if the certain TreeNode exists,
as the ORDER BY has guaranteed it.

A Category object would expose the following properties:

ID int
ParentCategory Category
Name String
Type int (or whatever)
SubCategories CategoryCollect ion

Finally, implement the CategoryCollect ion (inherit from
CollectionBase) . I recommend implementing two constructors:

sub new CategoryCollect ion(), and
sub new CategoryCollect ion(ownerCatego ry as Category)

This way, you can update your database when someone uses the
construct:

myCategory.SubC ategories.Add( aNewCategory )

Just a couple of quick points:

1) Cache your category objects - they tend to be used much, much more
often than they are changed.
2) Although possible, you do not need to implement a TreeCollection of
some sort. The basic CategoryCollect ion will suffice.
3) If you're implementing a multi-lingual system, factor the
CategoryName field into a different table (or build a view over the
table and use that instead).

hth,

Andy

[_Andy_]

Re: Collection Question

On Mon, 05 Apr 2004 01:11:06 GMT, "WStoreyII"
<papastoreyii@s bcglobal.net> wrote:
[color=blue]
>I am Making a Collection for categories
>
>There will be three fields
>
>Category Major And Minor
>For the Category i need to know what Type it is Expense/income
>for the major i need to know the cat id and the major id
>and for the minor the cat and the major id
>
>at least i think that is the best way to set it up which is actually my
>question
>what is the best method for this a datatable which is related a collection
>if so which one because i have seen a few of them
>
>I want to be able to use this collection to populate combobox wit h there
>perspective fields as wells as list boxs and also i would like to
>poplulate a tree with the parent the category the child the major and the
>grandchild the minor
>
>for example if the category were groceries the major could be fruits and the
>minor would be apples
>
>i was thinking the best way of doing this would be to make a collection of
>the three fields and each on have properties but do not know how do to do
>this A
>and b i dont know enough about collections in vb.net to decide which way
>will be the most efficient
>
>any ideas?
>
>WStoreyII
>[/color]

Getting the taxonomy right is an important part of any system design.
If I were you, I would consider implementing a standard tree structure
for your categories in a table, thus:

Table: CATEGORY
CategoryID int (not null - PRIMARY KEY)
ParentCategoryI D int (allow null)
CategoryName string
TypeCode int (1=expense,2=in come etc.)

Using your terminology: a Major category is one where the
ParentCategoryI D is null (or zero, if you prefer), all others are
Minor categories.

As you can see, you can implement a deeper tree structure at a later
date, if you need to, simply by changing the contents of the database.
A tree view would be populated simply by executing the SQL:

SELECT * FROM category ORDER BY ParentCategoryI D,CategoryName

That would guarantee that all the parent categories ("major") are read
first, and all categories appear in alphabetical order, at every tree
depth. There's no need to test to see if the certain TreeNode exists,
as the ORDER BY has guaranteed it.

A Category object would expose the following properties:

ID int
ParentCategory Category
Name String
Type int (or whatever)
SubCategories CategoryCollect ion

Finally, implement the CategoryCollect ion (inherit from
CollectionBase) . I recommend implementing two constructors:

sub new CategoryCollect ion(), and
sub new CategoryCollect ion(ownerCatego ry as Category)

This way, you can update your database when someone uses the
construct:

myCategory.SubC ategories.Add( aNewCategory )

Just a couple of quick points:

1) Cache your category objects - they tend to be used much, much more
often than they are changed.
2) Although possible, you do not need to implement a TreeCollection of
some sort. The basic CategoryCollect ion will suffice.
3) If you're implementing a multi-lingual system, factor the
CategoryName field into a different table (or build a view over the
table and use that instead).

hth,

Andy