Design suggestions for a tree structure

[fairyrak]

I have my plan, but I don't know if it's a good one, to see I can get a better one please someone here expert in access design: please think about the most standard or general treelike or mindmap structure in access and then suggest me table names and the amount of tables and especially the field names for it:

(A tree has siblings, parents, children, nodes, roots)

I want to: be able to consult any ascendency like who are the children of a node? who are the parents, whats is all the ascendancy hierarchy? what are the siblings of a certain hierarchy? That's what I want to design the database for

 

[Micron]

What you seem to want is not something I have experience with, but I recalled a web page that dealt with parent/child relationships. Seems that most or all of it is in one table, and the trick is knowing how to get it out. This of course, assumes the relationships you are dealing with are of the same entity type (e.g. people or animals) and not a situation where the child is not of the same type as the parent. Here's the page Microsoft Access tips: Self Joins

 

[RickXL]

Hi,

Making the same assumption as Micron, you only need two tables. One has two columns called Parent and Child. Both columns contain Keys. Each Key will point to a record in the second table that has all the required attributes for each Key.

Note: You may need to create a "fictional" Key for the top level in the hierarchy if one does not naturally exist.

If each Key is guaranteed to occur exactly once in the Child column then you could combine both the above tables. In this scenario, you have a complete list of Children and all their attributes plus a column for the Parent all together in one table.

I have seen systems where other tables were added to define the layout of certain outputs from the system to ensure, say, the order of the boxes across the page in an organisation chart.

The skill comes in writing your reports to extract that data and present it in a useful form.


Regards,

 

[Luthius]

Microsoft has an example:
http://office.microsoft.com/en-gb/templates/genealogy-database-TC001076524.aspx?pid=CT102144001033