Hi there,
I am building an app where food providers can see the ingredients needs of restaurants.
I have three tables.
Restaurant table - Basic info of the restaurant. (includes restaurant address)
Dishes table - It has info of the restaurants that offer each dish.
Ingredients table - It has info of the ingredients needed for a dish.
I want to relate the ingredients table and the restaurant table. End goal is to display in a map all the restaurants that use a given ingredient.
Hi, I am not sure how your dishes table is set up. Does it have an array of restaurants?
Having more information about your current relations would help to know what you want.
Just off the top of my head though,
Here is how I would structure it.
Restaurant table – simple list of restaurant information (name, number, etc).
“Restaurant Dishes” table has a list of each dish restaurant carries
Dish is the main dish record (prep instructions, whatever)
Dish Ingredients has a list of the ingredients needed for each dish
Ingredients would have info perhaps about cost/ounce – supplier, etc.
From there you could probably set up relations between the different tables to get what you need. The key is that each record that has a relation to a prior record would need an ID for what you want to create a relation to.
The thing is that even if every dish uses salt, you cannot just have one salt record. You have to have a record for each dish that lists salt as an ingredient with the Row ID of the ingredient and the Dish ID of the dish included in it. From this record you can tie back to the restaurant, because you have a list of all the dishes served by that restaurant which has its own Row ID and Restaurant ID.
In the restaurants table, create a relation that links the restaurant to the Dishes table. Create a Lookup column that returns all of the dishes for restaurant. Then using that lookup column, create another relation to the ingredients table. Finally create another lookup column that returns all of the ingredients from the Ingredients table. This will be an array. Depending on what you need, you can also use a Joined List column to get a comma delimited text string of ingredients from the second relation instead of a lookup array.
When I tried to do it, I had to add a 4th table called “Ingred Master.” My thought was that 2 dishes would both include an ingredient, so it made sense to me to have a master table of ingredients that would be your list. I wrote a skeleton app to demonstrate the relationships. There are not input screens. I just copy and pasted the various table ID’s to the various tables.
Take a look at all the relations and how they are linked. It may take a bit to understand, but it works. What I did is a little different than what @Jeff_Hager describes but it’s the same concept of using an Array column.
You can copy the app to take a look. There is very little data. Just try Salt, Egg, Beef.
Thanks a lot!
So do you guys recommend to always use a row Id in all your tables so that you can reference them from other tables as a good practice?
Also, about this specific example, do you think it is better design to have one table per restaurant with all the dishes that restaurant has or one big table with all the dishes of all restaurants?
@David_Gabler could you explain me why using a Row Id is a better idea than using your own unique identifier? For example, for uniquely identifying a dish, I was thinking of using restaurant_name + dish_name.
The advantage I see with using your own identifier is that when adding data to your DB in a manual way, it is easier to recognize that id, than the gebbrish Row Id.
What happens if you change the name of a restaurant or dish. Even something as simple as correcting a misspelled word. All of a sudden your relation is broke and you may not notice.
You can use any kind of unique value you want, but you must be 100% confident that the value you are using for your relation will never change. Names change. Row ID’s don’t.
You can write Names and ID’s to the table, but I would still use RowID for the relation. For your setup, I would add a RowID to the restaurant table. When adding dishes to the Dishes table, I would include the restaurant RowID. Then your relation can link the ID’s together. If you want to retrieve the restaurant name into your dishes table, then a Lookup column can pull that out of the relation.
You can certainly do that for some things. I’ve used template columns to build unique identifiers to create relations to prevent duplicate entries, etc. But they don’t take the place of a Row ID. It’s best to accustom yourself to using Row IDs precisely because they never change. Even if you think your data will never change, I assure you I’ve been doing this for 30 years. Did you know “Corn Pops” cereal used to be called “Sugar Corn Pops”? Same food.
