I am working on designing tables for work orders.
- There is different work order models (from now on called WOM)
- The WOMs share some attributes (Num, Date, Description, ... etc)
- The WOMs have details such as:
- Sectors on wich the work is done.
- Some WOMs use storage tank instead of sectors (products are prepared in storage tanks).
- Products and their quantities (plus or no some info on product) applied to wich sector.
- Human ressources wich worked on the WO.
- Materials used on the work order
- ... etc
What is needed
- Design tables for work orders and the details ofc.
- They want to know how ressources were spent.
- Design queries to retrieve all shape of infos.
- Simple presentation for the end users.
- Generalizing the work orders models.
What has been done
Designed all work orders and their details as a hierarchy starting from work order num as the mother node.
WorkOrderTable (ID, ParentID, Type, Value)
example of a work order Transform hierarchical data into flat table
ID ParentID Type Value 38 0 Num 327 39 38 Sector 21 40 38 Sector 22 43 40 Product NS 44 40 Product MS 50 40 Temp RAS 48 44 Quantity 60 47 43 Quantity 25 41 39 Product ARF 42 39 Product BRF 49 39 Temp RAS 51 39 Cible Acarien A. 46 42 Quantity 30 52 42 Cible Acarien B. 45 41 Quantity 20
Is what I am doing good/efficient easy to maintain work with or there is other ideas ?
UPDATE I : More details
- Products aren't changing about 50 active ones [products change over time, need to keep track of version]
- Sectors are about 40 (fixe land area)
- People normal HR table
- How Big is a typical WOM :
- about 15 attributes (3 of them mportante and shared by all WOMs the others are a little less)
- about 5 or more details sharing : Product, Sector, People and other describing infos like the quantity of the product.
- WOMs are fixe for now but I am worried about them changing in future (or the rise of new ones)
- The versionning isn't a requirement right now, but adding it is a plus.
- I am planning on using different tables for participants (sectors, products ...)
- The meta-data / data confilict is what this design dilemma is about.
- Considered any WOM is defined by 3 parts:
- The Work Order General Info (Num, Date, ...)
- The Sectors [Other WOMs use Tank storage] in wich the jobs are done.
- The Ressources to complete the job products, people, machines ...
State of the design
- Specific tables for participants sectors,people,machines...
Meta-data table (ID, meta-data, lvl). Example :
- Sector, 1 (directly to WO)
- Tank Storage, 1
- Product, 2 (can be part of sector job not directly to WO) sd
Work Order table (ID, parentID, metadataID, valueID) the value ID is taken from the participants table
Concerning XML I have so to no informations about how to store them and manipulate them.
UPDATE II [Code]
CREATE TABLE [dbo].[WorkOrders]( [ID] [int] IDENTITY(1,1) NOT NULL, [parentID] [int] NOT NULL, [metadataID] [int] NOT NULL, [value] [nvarchar](50) NOT NULL, CONSTRAINT [PK_WorkOrders] PRIMARY KEY CLUSTERED ( [ID] ASC )) CREATE TABLE [dbo].[MetaData]( [ID] [int] NOT NULL, [metadata] [nvarchar](50) NOT NULL, [lvl] [int] NOT NULL, CONSTRAINT [PK_MetaData] PRIMARY KEY CLUSTERED ( [ID] ASC )) CREATE TABLE [dbo].[Participants]( [Code] [int] NOT NULL, [Name] [nvarchar](50) NOT NULL, [customField1] [nvarchar](50) NULL, [customField2] [nvarchar](50) NULL, [customField3] [nvarchar](50) NULL, CONSTRAINT [PK_Participants] PRIMARY KEY CLUSTERED ( [Code] ASC ))
Query used to retrieve data by Shnugo
DECLARE @tbl TABLE(ID INT,ParentID INT,Type VARCHAR(100),Value VARCHAR(100)) INSERT INTO @tbl VALUES (38,0,'Num','327') ,(39,38,'Sector','21') ,(40,38,'Sector','22') ,(43,40,'Product','NS') ,(44,40,'Product','MS') ,(50,40,'Temp','RAS') ,(48,44,'Quantity','60') ,(47,43,'Quantity','25') ,(41,39,'Product','ARF') ,(42,39,'Product','BRF') ,(49,39,'Temp','RAS') ,(51,39,'Cible','Acarien A.') ,(46,42,'Quantity','30') ,(52,42,'Cible','Acarien B.') ,(45,41,'Quantity','20'); WITH recCTE AS ( SELECT t.* ,0 AS HLevel ,t.Type + N'="' + CAST(t.Value AS NVARCHAR(MAX)) + N'" ' AS attr ,CASE WHEN EXISTS(SELECT 1 FROM @tbl AS x WHERE x.ParentID=t.ID) THEN 0 ELSE 1 END AS IsLeaf FROM @tbl AS t WHERE ParentID=0 UNION ALL SELECT t.* ,r.HLevel+1 ,attr+t.Type + N'="' + CAST(t.Value AS NVARCHAR(MAX)) + N'" ' ,CASE WHEN EXISTS(SELECT 1 FROM @tbl AS x WHERE x.ParentID=t.ID) THEN 0 ELSE 1 END AS IsLeaf FROM @tbl AS t INNER JOIN recCTE AS r ON t.ParentID=r.ID ) SELECT CAST(N'<row ' + attr + N'/>' AS XML) FROM recCTE WHERE IsLeaf=1 FOR XML PATH(''),ROOT('root')