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.