Hi all,

I was wondering if anyone had any input on a design idea I might try.

I have a series of numbers which will be stored as text that are 14 or so digits long. Each pairing (or in some cases triplet) of digits in the number means something, and, some combonations of digits are only avaialble from certain ones and so forth, and certain sets of digits mean different things if preceded by different combonations of digits.

Now, I will have to capture all of these digits and what they mean.

The question I have is : Is there a logical / practical objection to a self linking table? Ie, the foreign key of the table links to the primary key of the table? I'm aware that it increases the possiblitiy of an infinite loop, but are there other objections / problems? If I needed to have a 1-M style relationship I could have a table of the <number><defintion> and a table of <NumberPK><NumberFK> of the links.

Does anyone know a reason / situation where this would be practical?

Always open to new ideas,

Adam

You don't say much about the actual structure of the table... however, self join is a common practice (when needed)... take a look at Northwind database, the employees table uses a self join (ReportTo field).

To enforce what Danny has indicated, "self-joins" are common ... That is how I store a lot of hierarchial data. The trick is to avoid circular refs ... that is why I use a TreeView control when manipulating data that is hierarchial, because the TreeView will NOT allow you to create an endless circular loop.

Another "got-cha" is cascading deletes ... if you delete a parent, you need to write code that will remove or promote the children.

Brent,

Its funny - I have the peices of data that I need to store, and how I was going to represent them was in a treeview control. While I was looking at the many many levels I thought that a self joining table would be the best way to go about it, but was unsure if there was either a) a large stigma against them or b) significant slow down in performance when accessing the table.

Thanks,

Adam

I have been working on a Code Archive sample called "Fun With TreeViews" ... but as with many things ... it has been slow going!!! ... I have a couple of apps with treeviews and drag and drops and all kinds of cool stuff ... its just hard to make it portable ... BUT ... I do have a module called "mod_TreeViewCommon" that I use to do the basic stuff of a tree ... AddNodes(), IsChild(), IsParent(), GotoFirstNode() ... and some other stuff if you are interested ... I will warn you though, TreeViews, and the code that controls them can be fairly large and daunting!! ... but once you get use to them ... I use them for so many things ... for example, I limit the use of cascading combos!!

Also, I will let you know when (and If) I "publish" the "Fun with TreeViews" utility ...

Hi Brent!

I am always admiring the presentation/manipulation of data/record using TreeView but no enough knowledge to do it.

Would it be possible also to notify/inform me when you publish you "Fun with TreeViews" utility?.. either way you can PM me here or on my email (cagay_anos@yahoo.com)..


Thanks a lot,
ca

Brent,

Treeviews resemble in structure a N-Tree, and many of the functions are those that are used with a binary tree. I have no trouble with the concept - in fact, I'm eager to try out designing a data structure in vb.

I've been wanting to create a simple treeview that will work with anything as well. So, I guess, here's my oppertunity. Thanks for the input.

Adam