SQL Server Full traceability of a product

Question

I'm creating a program that manages a manufacturing plant, and I need to show each product's traceability (all the paths it took from creation until it's final delivery. Let me show you an example: The plant creates the document A001, with quantity 400. Then, they need to split the product, creating documents B002 and B003, both with quantity 200 and both with their Parent field value of A001. After that, they'll split B002 into smaller pieces. That creates documents C004, C005, C006 and C007, all with quantity 50 and all with the Parent field value B002.These smaller pieces can also be split again...

Now, if I wanted to trace the full cycle of document B002, I'd check the Parent field and cross it with the document field to get that info, and then get the documents where the Parent field is B002. That's the "easy" part.

Now the tricky part.

I want to know the full cycle of document C007. I'd have to check his parent, and get the B002 document, THEN have to get that document's Parent and get the A001 document. I'd also check for documents with Parent C007 and find none.

Or know the full cycle of document A001. I'd check if there was any Parent (there won't be), they i'd have to get all the documents with Parent A001, then get all documents with Parent B002 and B003 and so on.

Is there any function on SQL that let's me do this, or do I have to create a procedure that recurs itself over and over to check for both parents and childs? And if so, I have no idea what to do, so any help would be appreciated.

Answer 1

Basically you ask for something simple that has been done thousands of times - find the root of a tree.

There are various approaches to that, among other things a special data type (HierarchyId) that supports that right in SQL Server.

https://msdn.microsoft.com/en-us/library/bb677290.aspx

is the documentation for this.

That said, you likely will use a normal field as ID - and then the best approach is a stored procedure.

http://vyaskn.tripod.com/hierarchies_in_sql_server_databases.htm

has some thoughts about it - as has google tons of it (there are various approaches to query them).

http://blog.sqlauthority.com/2012/04/24/sql-server-introduction-to-hierarchical-query-using-a-recursive-cte-a-primer/

is from quite a reputable source and using a CTE Like this:

WITH MyCTE
AS ( SELECT EmpID, FirstName, LastName, ManagerID
FROM Employee
WHERE ManagerID IS NULL
UNION ALL
SELECT EmpID, FirstName, LastName, ManagerID
FROM Employee
INNER JOIN MyCTE ON Employee.ManagerID = MyCTE.EmpID
WHERE Employee.ManagerID IS NOT NULL )
SELECT *
FROM MyCTE