Modelling a hierachy

[andershs]

I have a data modelling question, which I hope you will help me figure out. I am new to data modelling in BI, so please consider this.
I have a feeling this might be relatively straight-forward for more experienced users.

I have a database with a record of dishes in table Dish. Each dish has some characteristics: name, price, amount of pasta etc.
They also have a Pasta Type and a Pasta Type Group. The relationships between these seem to be circular.

The thinking is, that each dish can have a certain Pasta Type that it has to be made with. If no Pasta Type is present, there may be a Pasta Type Group, that the dish has to be made with.
Both Pasta Type and Pasta Type Group are nullable in the Dish table. They don't have to have a value.

If a dish entry has both Pasta Type and Pasta Type Group set (to not null), the most detailed information, Pasta Type, will win.

Pasta Types could be: 'Fettucine', 'Spaghetti', 'Tagliatelle' etc. Pasta Type Groups could be 'Tubes', 'Ribbons', 'Shapes', but also 'Whole-grain', 'Gluten-free' etc.

There exists a many-to-many relationship between tables Pasta Type and Pasta Type Group.

Below is an overview of the database. I have inserted a value 'None' for each dish if both Pasta Type and Pasta Type Group is null:

I have modelled this currently with a fact table covering the dishes made on a certain date.
Each dish has both a pastaTypeId and a pastaTypeGroupId. Both can be null/not defined.
Like this:

I would like a hierachy based on Pasta Type Group and Pasta Type, such that I can make a visual like this:

Since each Pasta Type can be a member of many Pasta Type Groups, I am struggling with creating a circular relationship like in the database above.

This however, gives me None in Pasta Type:

Moreover, I have added reference table and a duplicate Pasta Type table to the Pasta Type Group table like this:

But this means, that my summations of amount of pasta is not correct for the Pasta Types:

Any help on how to model this hierachy is appreciated.

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[lbendlin · Accepted solution]

It sounds like Pasta Type Group should be a parent dimension of Pasta.  Are there instances of Pasta that are not part of a group?

Moreover it sounds like dish should be a standalone table, not linked to either Pasta or Pasta Group, since there is no strict dependency.