0

I'm searching for a way to model 4 tables, keeping them consistent. 2 of the tables are a kind of 'enum++' types: they describe what is possible. The other 2 are each of them a concretization of the 'types'-tables. An example, simplified :) :

ActionType: describes the possible types of 'actions', e.g. cut vegables, cook, ...

  • name

ActionTypeResult: describes the result of each Type of action

  • name
  • type (<- Type.name)

So, for example, the result of cutting vegables would both be organic waste and cookable pieces of vegable (so 2 results). boiling would also have 2 results: cooked food and boiling water (you normally get rid of the boiling water, but it's the result of that step).

Now, I want to describe a recipe, which means, it has several ActionTypes, but the Result of an ActionType is the input of the next one. So:

I may have a Recipe entity, which consists of

CookingSteps, which links to the ActionType - to know what kind of step it is, and which kind of Results the step has. CookingFlows, which are the Results (the products) that can be the input of a next CookingStep.

So, one might do this:

Recipe:

  • name

CookingStep:

  • recipe (<- Recipe.name)
  • title (well, you can give the steps a name, dependent on the recipe :) )

CookingFlow:

  • step (<- CookingStep.title, this is the source of the flow)
  • recipe (<- Recipe.name, not sure if we really need that, since we know it because it is already linked by step, I didn't include in the diagram below)
  • result (<- ActionTypeResult.name, so know which of the different flows we're talking about)
  • flows to (<- CookingStep.title, so we know where to this flows).

Now, doing this, I see redundancy in the recipe relationships, but it is also possible to 'cheat': a CookingStep of type cut vegables can have a relationship with a CookingFlow which has a result boiling water or boiled food. I want that cheating to be disallowed.

The question is: how to model this properly?

The problem is that it can lead to inconstent data (the cheating). The main problem here I have is: having a certain CookingStep, I have both an ActionType and a CookingFlow. This is fine. However, The ActionTypeResult I have in the CookingFlow in this case must be one that is allowed by the ActionType defined by the CookingStep. I want the right ActionType to be enforced on the CookingFlow of the same CookingStep. I can use triggers on the DB to check if this is right; I was mainly wondering if one could model it without triggers.

enter image description here

philipxy
  • 14,867
  • 6
  • 39
  • 83
Kurt Sys
  • 11
  • 4
  • What exactly is your question?--Model what correctly? What is the problem? What is the "redundancy in the recipe relationships"? Why is it bad? What "doesn't seem right"? What is "right"? Use enough words, sentences & references to parts of examples to clearly & fully say what you mean. PS Putting words in scare quotes does not clarify the idiosyncratic meaning that you didn't make clear by actually saying what you mean. (For 'cheat' you give an example but don't say what it is an example of.) – philipxy Mar 12 '20 at 20:49
  • Please clarify via edits, not comments. PS You are still not clear. Where is the term "'enum++' type" from? Why the quotes? What "kind of"--exactly? You are still giving examples without clearly saying what they are examples of. What is a "concretization"--not clear. "cheat" in quotes--undefined--followed by an example--of what exactly? A "basically" or "essentially" or "in other words" etc that doesn't introduce or summarize a clear, precise & full description that you also give just means "unclearly". "So, for example"--An example of what? What are "<-" & "<- Type.name"? What "redundancy"? – philipxy Mar 30 '20 at 01:34
  • Be clear by writing the concise user manual to use this DB. When giving a business relation(ship)/association or table (base or query result), say what a row in it states about the business situation in terms of its column values. PS There are many diagram styles. Give a legend or reference. But: [use text, not images/links, for text--including tables & ERDs](https://meta.stackoverflow.com/q/285551/3404097). Use images for what cannot be expressed as text or to augment text. PS Re asking: Ask re where you are 1st stuck following your textbook/reference & give just what is needed to ask. – philipxy Mar 30 '20 at 01:44
  • Thanks for you answers, but is seems I'm not able to express it clearly, nor with images, nor in text. It is not only about subtypes/inheritance/polymorphism. I know how these work. That's something one can find in textbooks, so I don't need SO to answer that :). I'm not talking about tables here, or SQL, or .... It's about an information model. I don't bother about FKs and PKs yet. That are implementation details. I first want get the model right, before I decide on the technology to be used. But again, it seems I can't express myself well enough. Thanks for trying, though. – Kurt Sys Mar 30 '20 at 19:08

1 Answers1

0

You seem to want that in resultsin a cookingstep's action type has as result type the result type of its cookingflow. You seem to seek a declarative way to express that constraint, possibly in a variation on this design.

Add to cookingstep & resultsin a step's type--replace them by their conjunction/join with oftype. Now participations/FKs are by associative entity (step, type). Add to cookingflow & resultsin a flow's type--replace them by their conjunction/join with ofresulttype. Now participations/FKs are by associative entity (flow, type). Add has associative participation/FK in resultsin.

The new versions of the relationships/tables that we have ANDed/joined type relationships/tables into have that type data "redundantly"--it's already in the type tables. But this allows for constraining in SQL via its (pitiful selection of) declarative constraints rather than via triggers. Those declarations not only appropriately restrict the projections of the new design that give the old design but control the redundancy. But also we have to update multiple tables now where we used to be able to update just one.

How can I enforce second-degree relationships without composite keys?
How do you ensure values from a logging table match objects in other tables ?
Storing “redundant” foreign keys to avoid joins
Group dependency SQL design

PS Your design doesn't type step inputs--"Result of an ActionType is the input of the next one".

PS Under the RM (Relational Model) & ERM (Entity-Relationship Model) one can talk in terms of tables or the relation(ship)/associations they represent. A DB table FK corresponds to an ER group participation in a relationship. A FK constraint holds when subrows appear elsewhere uniquely, which is when/iff entities participating together participate together elsewhere once.

PS Every FK/participation characterizes a subtype relationship--referencing/participating values/entities vs a supertype of all that might; we just don't always call that subtyping "subtyping". You clearly have explicit subtyping--you even have values/entities acting as "Type" tags. You just needed to tag entities with their tags so that type data is present explicitly with an entity for typchecking via FK constraints.

How can you represent inheritance[/subtyping] in a database?
How do you effectively model inheritance[/subtyping] in a database?

philipxy
  • 14,867
  • 6
  • 39
  • 83