sql server - Database schema for hierarchical groups -

i'm working on database design groups hierarchy used foundation of larger system. each group can contain other groups, , 'devices' leaf objects (nothing goes below device).

the database being used ms sql 2005. (though working in ms sql 2000 bonus; solution requiring ms sql 2008 unfortunately not feasible @ time).

there different types of groups, , these need dynamic , definable @ run-time users. example, group types might "customer", "account", "city", or "building", "floor", , each type going have different set of attributes, definable user. there business rules applied - eg, "floor" can contained underneath "building" group, , again, these definable @ runtime.

a lot of application functionality comes running reports based on these groups, there needs relatively fast way list of devices contained within group (and sub-groups).

storing groups using modified pre-order tree traversal technique has upside fast, downside complex , fragile - if external users/applications modify database, there potential complete breakage. we're implementing orm layer, , method seems complicate using relations in orm libraries.

using common table expressions , "standard" id/parentid groups relation seem powerful way avoid running multiple recursive queries. there downside method?

as far attributes, best way store them? long, narrow table relates group? should common attribute, "name" stored in groups table, instead of attributes table (a lot of time, name required display)?

are there going performance issues using method (let's assume high average of 2000 groups average of 6 attributes each, , average 10 concurrent users, on reasonable piece of hardware, eg, quad-core xeon 2 ghz, 4gb ram, discounting other processes)?

feel free suggest different schema i've outlined here. trying illustrate issues i'm concerned about.

i'd recommend construct easiest-to-maintain way (the "standard" parent/child setup) , run @ least basic benchmarks on it.

you'd surprised database engine can proper indexing, if dataset can fit memory.

assuming 6 attributes per group, 2000 groups, , 30 bytes/attribute, you're talking 360kb*expected items/group -- figure 400kb. if expect have 1000 items/group, you're looking @ 400mb of data -- that'll fit in memory without problem, , databases fast @ joins when data in memory.