I am designing an Italian restaurant database, but I face some problems considering the database design, which lead me to a halt. The part that I find difficult developing is the dishes table. A main dish could be a pizza, or some kind of pasta, or other. So a pizza could have many pieces and a pasta dish could be requested with a different kind of pasta as a customer wishes. On top of that you can add extra ingredients or remove some in every dish, which ingredients come to a cost and a quantity in the database which should be updated per order completion.
So should make an ISA relationship and separate my dishes to a pizza, pasta category, from there I could have the main pasta used in the dish and custom size for the pizza. In this scenario when placing an order the kind of pasta should be specified if different as well as the pieces in the pizza. Afterwards the ingredients and price would be multiplied by a constant factor proportional to the number of pieces, in that way avoiding storing all the possible combinations in the database.
Would this be a good design, what else could it be done to avoid data replication and have a readable order table? Any help would be much appreciated.
Best Answer
If the restaurant is that complicated I would use a simple solution. I would have an ingredients table and a entrée table to reference those ingredients. For example is you have a choice of pasta, sauce, cheese, and a meat on the side. I would have a table to store that information. I would then have a desserts and drink table that is separate. You can store your cost in the ingredients table and then do a calculation. The order table would then reference all of the previous information. This is just my thought process, but it may work for you.