Generic data modelling of products in the Entity Framework (EF)

Everything is composed of something until you hit the fundamental elements of your domain. In a restaurant environment these would be what a chef would call ingredients, ie. stuff that comes from a food processing plant or a farm. However at the food processing plant these are the output rather than the input. I’ve tried to draw this below, it’s not complicated once you understand that it is a model of how things are.

So model this, using a table ProductBase

Ingredients, recipes, menus, etc. are all the same. They are items that are made up from other items, until you get to items that are generally agreed to be fundamental elements, e.g. lemons, salt, pepper, beef steak, etc.

So if you have a recipe A, that contains ingredients A1, A2 etc. then these ingredients A1,A2 could be a recipe (i.e. a list of parts that are processed to produce something), or something pre-prepared. Let the data model reflect this.

From the data model you don’t need to make distinctions between what we call recipes, ingredients, etc, I store all of these in a single products table, with ParentProductID,

The parent product is used to provide containers – a list is defined by its contents.

The Linked product is used to define the container elemenent reference, i.e. a recipe has a list of ingredients, so there is an entry of type INGREDIENT, which has LinkedProductID of the actual product used.

CREATE TABLE `productbase` ( `productid` CHAR NULL, `productname` VARCHAR NULL, `parentproductid` CHAR NULL, `linkedproductid` CHAR NULL, `categoryid` CHAR NULL, `supplierid` CHAR NULL, `type` CHAR NULL, `subtype` INT NULL DEFAULT NULL, `cost` DECIMAL NULL DEFAULT NULL, `mcu` CHAR NULL, `mcuperpack` DECIMAL NULL DEFAULT NULL, `quantity` DECIMAL NULL DEFAULT NULL, )

Within the EF the type field is used to map to individual data entities thus:

Complete data model;

Taking the following Relational Database Schema

We map this to the Entity Framework using inheritance by specialization based on field contents.