Search code examples
mysqldatabaseormumlclass-diagram

Translating UML aggregation in a relational database

I'm making a JavaFX desktop application. It's a Point of sale system that tracks orders in a restaurant. I am very new to this and things got confusing after I fired up my phpmyadmin to create my database.

This is the relevant part of my UML :

Some examples to clarify the tables' content :

  • Ingredient table may contain : Flour, sugar, beef, eggs...
  • ArticleMenu table may contain : Pizza, Burger...
  • ArticleMenu is made of a bunch of ingredients , but these ingredient can still exist on their own, so it's an aggregation I thought.

The issue is to translate this into a relational database, and especially the aggregation.

What I tried : in ArticleMenu, the 'recette' attribute is a FK that refrences a row from Ingredient table, the 'recette' attribute is of type ENUM , which can hold many values the user defines when creating a row in ArticleMenu.

Example : ArticleMenu table has a row that represents let's say Pizza, one of it's attributes is recette of type ENUM and has the values "Cheese, flour, yeast, onions, tomato, mushroom, oil"

My question : is this an acceptable way of representing an aggregation and what would be the most optimal way?

Edit :

After reading Christophe's answer it's clear that I did not understand what ENUM is.

The IBM document he linked cleared a lot of confusion I had relating to this topic.

Stock information shouldn't be part of the ingredient table since there could be many states of the same ingredient, so I removed those.

As for the quantities used in each recipie, those will be represented using the quantity attribute from the recipie table.

So I re-thoought my approach and came up with this design: enter image description here

Solution

  • Problem with your approach

    You implemented what Martin Fowler calls a foreign key mapping, which implements a one to (potentially) many relationship:

    • Here, Recipe ('recette') would implement a many-to-one relationship between ArticleMenu and Ingredients: one article would have only one ingredient, and one ingredient can appear in many articles.
    • Moreover the ENUM lets you chose for one row only one value among severals. The ENUM is in fact just a handy replacement for using a number instead of a string.

    So, no, it's not the right approach.

    Many-to-many association and the hidden table

    What you need is to implement a many-to-many association: each ArticleMenu could have many Ingredients, and conversely each Ingredient could be used in many ArticleMenu.

    In an RDBMS, this can be implemented using an association table. It's a table taht is not visible in your conceptual model. This association table could for example be called Recipe and have two columns: idArticleMenu and idIngredient. You can then find:

    • all the ingredients of an article, by looking for every recipe row that has the relevant idArticleMenu.
    • all the articles in which an ingredient is used, by searching for the idIngredient

    Completing your model

    Now if you speak of recipe and flour, the next question in your application would be: how many flour do I need to make 1 pizza ?

    Unfortunately, this Quantity is not a property of ArticleMenu, since every ingredient of the same article could have a different quantity. It is not either a property of Ingredient, since an ingredient is used with different quantities, depending on the article for which it is used. So where to put this ?

    The answer is an association class

    Additional advice

    You can use the aggregation to express the whole-part relationship. However the aggregation semantic is not clearly defined in the UML specification. So there is no fundamental benefit in using it. You could therefore use a normal association here.

    In a design model (conceptual), you should not show in a class the properties that implement a relation. You could show the name of such properties at the other end of the association it will implements. If an implementation model, you could very well show all the columns of a table. The usual practice is then to use a «table» stereotype for each class that would be mapped to a table.