Java database management system to handle nested/tree data

I need to build a system in Java, to keep machine breakdown records. But this has become a bit complex for me.

The requirement is like this.

When entering a breakdown record, the user should be able to select a machine (say from a combobox). When a machine is selected, a combo box will appear below to show the sections of the machine. Once a section is selected, if the selected section has subsections, another combo will show those subparts... This goes like that until we come to the expected final part of interest. Then only the user can enter some description about the breakdown..

I hope that part is clear.

Then there's another requirement, searchability. It is also similar to above. Records should be able to search under all the above fields. That means, if a machine has several levels of subsections, we should be able to search under all those levels...

I've built a system to deal with machines having two levels of subsections. But that's not doing the job. So I've started over (several times :D)..

I'm using SQLExpress 2008. But I'm not much experienced with that. Since I don't know how to use SQLExpress to handle that nested/tree structured data. I've thought of using XML. Yeah, it's a bit ugly because other than the database I've to maintain a folder of XML files containing nested data. I thought I've done that.. until I remember about searching requirement.. :(

Finally, I gave up. And suddenly remembered the forum! Can someone help me with this. This is a training project assigned to me. I like programming, but I don't have that much of advanced knowledge/experience. This dynamic nature and the requirement to handle nested data confuses me.

Any help is greatly appreciated. Thanks!


ANSWERS:


Database design is not completely intuitive or easy. Learning by trial and error can take a long, long time. It's better to learn a few fundamental concepts and learn how to apply them to several cases before you try to learn by experimentation. Full stack has already pointed you in the right direction. Here's a summary:

There are several ways to store tree structured data in SQL tables. There is the adjacency list technique, using a separate column, call it "ParentId" that references the Id column in a different row of the same table. You can use this to create a tree of any desired depth, but searching it recursively is not easy. You will generally have to use product specific extensions to the SQL language or do the tree search in your programming language. The latter choice wastes almost all the power that a good DBMS puts in your hands. There's another approach, called the nested set model. This is a little harder to update when you need to put new leaves in the tree or turn a leaf into a branch. But query works really well.

Both of these approaches presuppose that you understand the concept of foreign key reference to a primary key, and that you can work with the relational model in many-to-one or many-to-many situations. Creating a database that is equally suited for searching on all the attributes is a non trivial design problem. Don't tackle it until you've mastered the elementary stuff.

Database design, when you do it right, is not really terribly complex. It's easier to understand a hundred tables than a hundred thousand lines of code. But it's more abstract than programming. It can be hard to learn to express requirements in terms of data declarations intead of action oriented imperatives.



 MORE:


 ? Database design; Dynamically creating tables
 ? Dynamic equivalent of Python's "from <module> import <symbol>"
 ? Dynamic equivalent of Python's "from <module> import <symbol>"
 ? Dynamic equivalent of Python's "from <module> import <symbol>"
 ? Dynamic module import in Python
 ? Dynamic loading of python modules
 ? how to "reimport" module to python then code be changed after import
 ? free of doubly linked list
 ? What is the significance of Dynamic data structure(like linked list)
 ? Dynamically Create Linked List in C