I want to make an inventory system where items have multilevel packings e.g A carton has medium boxes , medium boxes has small boxes and small boxes has 2 items. Please tell how to handle it by using sql server 2005 so that it can easily handle sale and purchase and it display exact inventory status of how much carton , medium, small boxes and items are in stock.
+1
A:
I would recommend nested sets for hierarchical data in a relational database.
Andy West
2009-12-12 10:41:46
+1 for suggesting nested sets, but please remember that it's not an ideal solution that you should always use for all RDBs - it's a workaround if the database doesn't support heirarchical queries natively.
Mark Byers
2009-12-12 10:47:38
That's a good point. It turns out SQL Server 2005 and later support recursive CTEs. Thanks for making me aware of this option. Here's a performance comparison: http://explainextended.com/2009/09/25/adjacency-list-vs-nested-sets-sql-server/
Andy West
2009-12-12 11:03:10