I am working on adding a few features in a mobile application of a restaurant chain and the tech stack used is Java for Android and PHP + SQL for backend.
Until now I have always used NoSQL, but since this app is already in production & uses SQL they require me to continue using the same tech stack.
This chain has 9 outlets in the district and in each of their outlets they have a 'Five in a box' meal where you can choose any five things from their menu and order them under the title of 'Five in a box' with a discounted price than if you order them separately.
Now the task is to keep a track of what 5 things each customer ordered and from which outlet did they order. Each night a Cron job will determine which combination of 5 things is the least ordered that day and then the users who ordered that particular combination get another discount coupon. (They also need to keep all of the old records as well for analysis.)
What I have in mind is:
A restaurant1_offerorder table which will contain the following columns -
userid - ID of the user who made that order,
date - date of order,
item1, item2, item3, item4, item5 (five columns) which will contain the ids of the food item that the user bought.
And since each outlet is projected to have at least 700+ 'Five in a box' meal orders everyday a restaurant1_offerorder_old table with the same schema as restaurant1_offerorder which will contain all the old order data.
Every night the Cron Job will insert all records from restaurant1_offerorder to restaurant1_offerorder_old
This way the database will have 2 tables for each restaurant (18 tables in total which I feel is the wrong approach)
Now,
Question 1:
Is this the right database structure for this scenario? And should there be a separate database for each restaurant to store the old order data since soon there will be a lot of orders? Or will it be better to keep all orders in one table and SELECT * with today's date? (Consider the volume of orders say a year from now)
Question 2:
(Not to be implemented. Just curious and want to future proof the db structure and keep scalability in consideration)
Say this system needs to be scaled up and turned into a food ordering application where any restaurant can list themselves on the application and can opt in to the 'Five in a box' meal deal (from their own menu), would creating a new table for each restaurant's 'Five in a box' meal orders and a table for their old orders be the right approach? (Seems like a bad idea considering a rise in the number of restaurants and number of users)