(Draft -Incomplete )How to design tree or hierarchical strucutre in data base ?

This article subsumes -
What are current ways to design tree structure ?
Comparision of all of them ?
Flat table approach and advatages ?














dumb approach














Clever approach

How to get second highest record from table?

Generally a daft question asked by a nincompoop to other dunce who think that this is a lallapalooza of ones's knowledge of data base queries ?(none of wasn't me :) )
Don't throw conniptions at him because it is very difficult , I think you should realize that how easy it is and it is a bugaboo to just scare away you .

guess which question ;) ?

How to get second highest record from a table in MY SQL ?
or generic how to get Nth highest record in MY SQL ?

Consider following table















How to get second highest record from a table in MY SQL ?

SELECT name , salary FROM employee WHERE salary < (SELECT max( salary) FROM employee ) order by salary DESC LIMIT 1 OR using generic query N =2 , Offset =1

SELECT name, salary FROM employee order by salary DESC LIMIT 1,1

To get Generic Nth Highest record

SELECT name, salary FROM employee order by salary DESC LIMIT (N-1),1

LIMIT X, Y --> suggest that X is row from which onward to start and Y is number of rows or offset

example of limit clause

SELECT * FROM tbl LIMIT 5,10;  # Retrieve rows 6-15

What to look in code review ?

Coding Guide Lines

For some Team leads code review is like just one afternoon free from work .What these guys look in code is indentation , naming convention and all crap which is not going to reduce bugs in QA or in acceptance testing .Actually , such activities are overhead from companies . Under the name of code review it is a real time pass with vacuous comments.
For static code review we must rely on automated checking using like find bug , check style PMD etc .Many people don't know to calculate dependency , complexity of code , how to calculate cyclomatic complexities of code .Just review of indentation , formatting is useless and overhead.
In design review, which must happen at after requirement gathering stage . These people must be well versed with design patterns .At least they must know gang of four patterns .Some people I have seen do excellent procedural coding in java which is very upsetting and frustrating .

Buzz words of OOP coding like loose coupling and adhesive coding should be found in such reviews .Most important thing is neglected is thread safe coding .Eventhough These bugs are very hard to catch in code review , we should at least try to catch them early stages of project because in next staged cost of fixing is very high and futher at customer end is the highest cost and loss of trust .
The most important thing is that reviewer does not consider amount of data handled by method .If large amount of data to be processed then inefficiency of loop is going to magnify and it will cause more response time.
Proficient reviewer must catch such errors before causing serious damage to customer.
While coding consider the situation that next person who will be coding , is a desperado , reckless outlaw with three counts of felony and further more he knows your address. ;) .Or you may imagine that next coder is demon and his doppelganger is going to haunt you if you make you code inflexiable and not readbale , inefficient .

What is cost to the company?
It is really time consuming and extractives with no real time out put .So do code review for hot spots like critical module, new comer who don't know code. Instead of making long list of does and don't , real implementation of recommendations is important .Code review should be done in minimum time with maximum awareness of nasty design related bug and not just grammar check of code .shouldn't it ?

In general
1. One function should be maximum of 25 lines. Use proper indentation.
2. Follow naming conventions for classes, methods and variables.
3. Refractor the complex code in simple pieces whenever possible.
4. Put informative comment to important methods and update the same when logic is changed.
5. Minimize code complexity by reducing nested of conditional statements
(if else , while , for) statements inside one another.
6. Reduce dependency of one class on another .Too many import statements suggest more dependency and less flexibility.
7. Remove old commented code which clutters uncommented code.
8. Do not waste time to optimize, generalize the code which will be used only once.
9. Concentrate on optimization, efficiency of module on which many modules are dependent.
10. Avoid copy paste of code .Write proper reusable function for it.
11. Consider reuse of repeated and critical code.

In specific to Java
1. Coding style related
Pease refer to
http://www.oracle.com/technetwork/java/codeconv-138413.html

2. Standard practice related

1. Use private variables and give set method only if necessary.
2. Avoid try {} catch (Exception ex) .Be specific about thrown exception.
3. Try to avoid null. Return empty object in case of collection.
4. Always override hash code when you override equals
5. Select proper collection for time efficient methods.
6. Do not make public class level variables
7. Do not synchronize methods unnecessarily.
8. Always override toString() methods for data models.
9. Use comparable interface than comparator.
10. Consider thread safety when multiple threads are accessing the object.

For more please refer to

http://www.javapractices.com/home/HomeAction.do
http://www.havelund.com/Publications/JavaCodingStandard.pdf
http://www-01.ibm.com/software/webservers/appserv/ws_bestpractices.pdf


Code review
A. Static/Automated Code review
Rely on automated code review for coding style and naming convention and not for logical or design related bugs .
1. Use of PMD
Link - http://pmd.sourceforge.net/


2. Use of check style
Link - http://checkstyle.sourceforge.net/


3. Use of find bug
Link - http://findbugs.sourceforge.net/


4. Use of tools like Jdepend, TC tool to determine complexity, dependency Index of class.
- I Will be adding information on Jdepend , TCtoolkit ,SourceMonitor ,GraphViz ,DependencyFinder
Depend-o-meter etc.


B. Logical/Design related Code review

• Correct Design patterns
Link -

• http://java.sun.com/blueprints/corej2eepatterns/Patterns/index.html
  • Modular Programming
  • Flexibility for future requirement
  • Object oriented coding (Some people (from C++ )write exellent procedural code in java : ( )
  • Consider amount of data to be handled
  • Inefficient loops
  • Avoid copy paste of code at many places.
  
  
  References
  http://en.wikipedia.org/wiki/Code_conventions
  http://pdfcast.org/pdf/effective-java-programming-language-guide
  http://www.sourceformat.com/coding-standard-java.htm

-- NILESH SALPE

(NOTE : This blog is draft - no grammar check )