How to model 2x2 entities (relational tables), in which 2 describe the 'model' and 2 are 'concretizations'?

Question

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.

Answer 1

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?