Hello stackoverflowians,
I am working on designing tables for work orders.
The problem:
- There are 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 which 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 which sector.
- Human resources which 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 resources were spent.
- Design queries to retrieve all shape of information.
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 important and shared by all WOMs the others are a little less)
- about 5 or more details sharing : Product, Sector, People and other describing information 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 versioning 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 conflict 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 which the jobs are done.
- The Resources 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 information about how to store them and manipulate them.
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...?
- 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