0

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!

Anubis
  • 6,995
  • 14
  • 56
  • 87
  • *Finally, I gave up.* And now you think that someone here at SO should just continue doing **your** job? – maba Sep 25 '12 at 10:26
  • 3
    @maba It's a training project. So it's Ok to ask for help at SO.. –  Sep 25 '12 at 10:31
  • 1
    That's a legitimate question; otherwise, you'd get some code samples etc. in response. The key in such a situation is try to formulate your question is a googlable way. Here's my take: "storing hierarchical data in relational database." And it produced a brilliant overview: http://stackoverflow.com/questions/4048151/what-are-the-options-for-storing-hierarchical-data-in-a-relational-database . Hope it will show you the next step. – full.stack.ex Sep 25 '12 at 10:38
  • Thanks @full.stack.ex appreciate your help. I was not trying to cheat or something. I tried almost all the approaches according to my knowledge. (Actually, though I said given up, I'm still trying). But the complexity drives me mad (really). That's why I came here looking for some support.. – Anubis Sep 25 '12 at 10:47
  • 1
    Go ahead step by step and keep asking yourself and others smaller questions. You'll make it this way. – full.stack.ex Sep 25 '12 at 11:07
  • Thanks @full.stack.ex I'm going through the article you pointed out. In the mean time, I also found [this article](http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/) helpful.. Hope it'll help someone else too.. – Anubis Sep 25 '12 at 11:11
  • 1
    Bill Karwin gave a [nice presentation](http://www.slideshare.net/billkarwin/models-for-hierarchical-data) on the topic of hierarchical data. In your case, you'll probably just use the "adjacency list" model, but it doesn't hurt to know what else is out there... – Branko Dimitrijevic Sep 25 '12 at 11:26

1 Answers1

3

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.

Walter Mitty
  • 18,205
  • 2
  • 28
  • 58