2
\$\begingroup\$

I am working on designing tables for work orders.

The problem:

  • 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.

Constraints

  • 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

The Question

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')
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.