1

Building functionality so that an admin can build the workflow for a product.

  1. The process driver is a customer-sku combination. Each of which needs a workflow.

  2. A workflow consists of the departments of the warehouse which the product must move through. Examples of departments are: "Shipping", "Receiving"

  3. Each department contains multiple stages, time requirements, and a queue place holder (for keeping the departments in the correct order). Examples of stages are: "QC", "Clean", "Box"

  4. Each stage contains multiple steps, time requirements, and a queue place holder (for keeping the stages in the correct order). Examples of steps are: "start", "stop", "hold"

Is this a bad design?

image of tables

Brent Connor
  • 628
  • 2
  • 8
  • 23
  • 1
    (a) Where is Customer (in relation to your description). (b) Why is it a *Customer* SKU, and not simply an SKU ? (c) Do you have one workflow per Department/Stage/Step (as you have modelled) ? If so why, why not one ? – PerformanceDBA Jun 05 '15 at 20:35
  • `customer_sku` is a pivot table of `customers` and `skus`. It's a weird setup, but I need for that to be what drives this whole process. SKU might be better thought of as `project type` or `order type` – Brent Connor Jun 05 '15 at 21:03
  • 1
    Either fix the weirdness, or propagate it into everything else, eg. this enhancement. (c) ? (d) Do you need just the current workflow position for each SKU, or do your need history of steps for each SKU ? – PerformanceDBA Jun 05 '15 at 21:45
  • 1
    How are your SQL coding skills ? If I give your a model, can you understand it and code from it, in order to verify that it gives you what you need ? It will not look like your diagram. – PerformanceDBA Jun 06 '15 at 11:50
  • Depending on what kind of model you're talking about.. I just really need another perspective on this. – Brent Connor Jun 08 '15 at 12:26
  • Yes, I need history also. – Brent Connor Jun 08 '15 at 12:33

1 Answers1

3

Relational Answer

This is in case you want a Relational Database, with the Relational Integrity (as distinct from Referential Integrity), Relational power (at your level of question, that means JOIN power), and Relational speed, that Record Filing Systems (typified by Record IDs as "primary keys" in every file) do not have. That is all I provide.

  1. You can't model anything when your starting position is spreadsheets of what you think, with an ID field stamped on each of them.

  2. In addition to that problem, ID fields create many others. Please read this Answer from the top to False Teachers.

  3. Yes, too many n::m tables is bad. In the sense that it is a red flag indicating that the data is not fully Normalised. Which of course, must be completed before anything else is done.

Try this, this is what I think you need, given the spare requirements (feel free to be more specific).

Workflow Data Model

Data Model

  • That is an IDEF1X data model. IDEF1X is the Standard for modelling Relational Databases. Please be advised that every little tick; notch; and mark; the crows foot; the solid vs dashed lines; the square vs round corners; means something very specific and important. Refer to the IDEF1X Notation. If you do not understand the Notation, you will not be able to understand or work the model.

  • IDEF1X data models are rich in definition, something that UML and other diagrams cannot even approach. Refer to Data Model Anatomy for assistance in reading the data model, a different perspective to the Notation document.

Solution

  • Look at the Current Step Only model, and get your head around that, before contemplating the History model.

  • Compound Keys are standard fare in Relational databases. In fact that is the only way to obtain Relational Integrity (as distinct from Referential Integrity) and you need all the integrity that you can get in this scenario.

  • See if you can code from the model, and obtain all the reports and queries that you need, with a single SELECT each. If you need help, please ask.

Please feel free to comment or ask questions.

Community
  • 1
  • 1
PerformanceDBA
  • 32,198
  • 10
  • 64
  • 90