I'm very new to all of this. Let's say I want to make a site for various chefs worldwide(potentially a massive collection of data). On my index page, it would list individual "posts" displaying the chef and either 2 or 3 of their specialty dishes.
chefstable [id, chef_name]
dishtable [id, chef_id(foreign key), dish1, dish2, dish3(nullable)]
Upon clicking on this link, a new page will load with a more detailed view, including the above + ingredients for each dish and individual cost for each ingredient.
ingredientstable [id, dish_id(foreign key), ingredient1, ingredient2, ingredient3, ingredient4...(`nullable`), ingredient1cost, ingredient2cost, ingredient3cost...(nullable)]
Would it be more feasible to consolidate some of this data into one table, or are they ok separated into different tables that I will link together. Does anyone have ideas on a better way?
I am worried about the structure because there will be a lot of null values (dishes will have varying amounts of ingredients). Any guidance would be much appreciated.
I would go with this structure:
chefs
id
chef_name
--
dishes
id
chef_id FK
dish_type_id (1,2 or 3)
value
--
ingredients
id
dish_id FK
ingredient_id (1,2,3 or 4)
ingredient_cost
value