title: Database schema for hierarchical groups
body: <p>I'm working on a database design for groups hierarchy used as the foundation of a larger system. Each group can contain other groups, and also 'devices' as leaf objects (nothing goes below device).</p>
  
  <p>The database being used is MS SQL 2005. (Though working in MS SQL 2000 would be a bonus; a solution requiring MS SQL 2008 is unfortunately not feasible at this time).</p>
  
  <p>There are different types of groups, and these need to be dynamic and definable at run-time by users. For example, group types might be "customer", "account", "city", or "building", "floor", and each type is going to have a different set of attributes, definable by the user. There will also be business rules applied - eg, a "floor" can only be contained underneath a "building" group, and again, these are definable at runtime. </p>
  
  <p>A lot of the application functionality comes from running reports based on these groups, so there needs to be a relatively fast way to get a list of all devices contained within a certain group (and all sub-groups). </p>
  
  <p>Storing groups using <a href="http://www.sitepoint.com/article/hierarchical-data-database/2/" rel="nofollow noreferrer">modified pre-order tree traversal</a> technique has the upside that it is fast, but the downside that it is fairly complex and fragile - if external users/applications modify the database, there is the potential for complete breakage. We're also implementing an ORM layer, and this method seems to complicate using relations in most ORM libraries.</p>
  
  <p>Using <a href="http://msdn.microsoft.com/en-us/magazine/cc163346.aspx" rel="nofollow noreferrer">common table expressions</a> and a "standard" id/parentid groups relation seem to be a powerful way to avoid running multiple recursive queries. Is there any downside to this method? </p>
  
  <p>As far as attributes, what is the best way to store them? A long, narrow table that relates back to group? Should a common attribute, like "name" be stored in a groups table, instead of the attributes table (a lot of the time, the name will be all that is required to display)? </p>
  
  <p>Are there going to be performance issues using this method (let's assume a high average of 2000 groups with average of 6 attributes each, and average 10 concurrent users, on a reasonable piece of hardware, eg, quad-core Xeon 2 Ghz, 4GB ram, discounting any other processes)?</p>
  
  <p>Feel free to suggest a completely different schema than what I've outlined here. I was just trying to illustrate the issues I'm concerned about. </p>