Generalizing work orders

Question

Hello stackoverflowians,

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

Answer 1

Without knowing any numbers and further knowledge about your needs no good advise is possible. Here are some question coming into my mind

• How many users?
• How many products/locations/sectors/people...?
  • Is this changing data?
• How many WOMs?
• How big is one typical WOM?
• Is it a plain tree hierarchy
  • If not: Might there be alternative routes, circles, islands?
• Are these WOMs fix or do they change?
  • If changing: Do you need versioning?

It looks like trying to re-invent a professional ERP system. As Bostwick told you already, you should rather think about to use an existing one...

Just some general hints:

• Do not use the WOM-storage for (meta) data (only IDs / foreign key)
• Try to draw a sharp border between working data and meta data
• Use a dedicated table for each of your participants (sectors, products...)
• A WOM might be better placed within XML
• Read about (Finite) State Machines
• Read about state pattern
• Read about Business-Process-Modelling and Workflows

Answer 2

I think if your looking for design advice, you should go to a meta stack group, i.e code review exchange

That being said, your asking for advice on design, but only giving abstract information. The scale of a system, the amount of CRUD expected, and several other factors need to be considered in design. With out getting the details and targets its really hard to answer your question. There are trade offs with different approaches. It may even be advisable to use a nosql solution.

That being said, I would suggest not building your own ERP system, and instead look to buy one from a vendor, that is industry specific, and apply your customization to it.

Its very expensive to write your own system, keeping it updated, adding security, and a lot of others features make it a worth while business decision to purchase from a software vendor.

If your just looking to gain more experience by writing this, I would suggest browsing github, and the previously mentioned stack exchange.