SQL Server Database Design Problem/Challenge

Question

I have a database that has node & nodetype tables.

Nodes table

NodeID
ParentNodeID
NodeTypeID
NodeName
...

NodeType Table

NodeTypeID
ParentNodeTypeID
NodeTypeName
.....

Both tables have a relationship to itself.

There are different types of node i.e Node Site Building Office

These are hierarchical, so information (attributes) that is applied to i.e Nodes of type Site, should propagate down to, and be overridable by its children.

What is the best way to achieve this? Or am I looking to do to much in SQL and should this be handled in code?

UPDATE

NodeID    ParentNodeID   NodeName   Address1    Address2   Address3  NodeType  NodeTypeID
1         null           Top        null        null       MyTown    Site      7
2         1              Level1     null        HeadOffice MyTown    Building  8
3         2              Level2     SalesFloor  HeadOffice MyTown    Floor     9

Answer 1

I would have hold the NodeTypes that are specific to the node in the database, and handle the roll-up of nodetypes in the Business Layer. That way if something changes on a parent item, you only have to update 1 record in the database, not a large number (with all the associated sub-updates to drill down the hierarchy).

Answer 2

I would assume that the Node table also has a NodeTypeID on it? Otherwise I think the way you have the data stored is fine.

The biggest challenge you'll run into will be figuring out what the "effective" properties of a node/node type are by querying its properties, its parents properties, etc. Doing all that with SQL would likely be a nightmare and is probably better left to the data access or business layer of your application where it would be much easier to implement.

Answer 3

I would do the following

NodeTypes

NodeTypeId as INT
ParentNodeTypeId  as INT
NodeDescription as VarChar (100)

• ParentNodeTypeId ties to NodeTypeId for a self join

Nodes

NodeId as INT
ParentNodeId as INT
NodeTypeId as INT /* This field must be NULLABLE */
NodeDetails as VarChar (100)

• ParentNodeId ties to NodeId for a self join
• NodeTypeId ties to NodeTypeId from NodeTypes table, but is Nullable (read on)

I would have a recursive function called GetNodeTypeForNodeId (@NodeId as Integer) to figure out the node type. If current row has a non-null return the current value, else go up the parent-node chain until you find a parent that has a non-null value.

Note that this would get extremely expensive on large sets of data. I would avoid using the function until my result set has been completely defined - in other words, use sub-queries or CTEs to get the basic filtering, and then use on the function to get the NodeType.