normalizing product recipe data

Question

I am trying to implement product recipe's (thats what our supplier is calling it) but cant seem to wrap my head around how to normalize it properly.

I have added some sample data to illustrate what it looks like.

The values starting with R*** are references to recipe identifiers. The numeric values are references to product identifiers.

Recipe's are groupings of products (nothing more nothing less). The only attribute a recipe has is a name. This should be a logical name for the product grouping.

As you can see products in turn can also be connected to recipes. And products can be connected to other products directly.

The only restriction to this is that a recipe (R***) can never be connection directy to another recipe. So just to be clear, products can be connected directly but recipes can't.

The fact that a subarticle can have many different parents makes it kinda fuzzy for me.

Answer 1

Ok... came up with the following solution which seems to work out pretty good.

and in case anyone is interested, here are the sql creates:

CREATE TABLE recipe_node (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  recipe_id INT,
  product_id INT,
  created_at DATETIME NOT NULL,
  updated_at DATETIME,
  deleted TINYINT NOT NULL DEFAULT 0,
  deleted_at DATETIME,
  PRIMARY KEY (id),
  UNIQUE INDEX UNQ_PRODUCT_ID (product_id),
  UNIQUE INDEX UNQ_RECIPE_ID (recipe_id),
  CONSTRAINT FK_RECIPE_NODE_RECIPE_ID_RECEPT_RECEPTID FOREIGN KEY (recipe_id) REFERENCES recept(receptid) ON UPDATE RESTRICT ON DELETE CASCADE,
  CONSTRAINT FK_RECIPE_NODE_PRODUCT_ID_PRODUCT_PRODUCTID FOREIGN KEY (product_id) REFERENCES product(productid) ON UPDATE RESTRICT ON DELETE CASCADE
) ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_general_ci;

CREATE TABLE recipe_graph (
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  parent_id int(10) unsigned NOT NULL,
  child_id int(10) unsigned NOT NULL,
  quantity int(10) unsigned NOT NULL DEFAULT '1',
  sequence_number int(10) unsigned NOT NULL DEFAULT '1',
  created_at datetime NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY UNQ_PARENT_ID_CHILD_ID (parent_id,child_id),
  KEY IDX_CHILD_ID_PARENT_ID (child_id,parent_id),
  CONSTRAINT FK_RECIPE_GRAPH_CHILD_ID_RECIPE_NODE_ID FOREIGN KEY (child_id) REFERENCES recipe_node (id) ON DELETE CASCADE,
  CONSTRAINT FK_RECIPE_GRAPH_PARENT_ID_RECIPE_NODE_ID FOREIGN KEY (parent_id) REFERENCES recipe_node (id) ON DELETE CASCADE
) ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_general_ci;