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)
Best Answer
That way you will need to have a separate table for every outlet of the restaurant, and this will make the entire system difficult to manage with a lot of tables. It doesn't make sense to have multiple tables with the exact same structure.
Read more - Is it better to have large tables or many tables
Instead you can consider adding the outlets to another table and add the ID of each outlet as another column into the
offerordertable. That way you can have a single table for managing the Five in a box orders of all outlets.This isn't really necessary. Inserting less than 10000 rows per day (I say 10000 because you mentioned that there are 9 outlets with >700 entries/day) into a table having all integer columns of fixed size isn't going to slow down read operations noticeably (because apparently there aren't (m)any UPDATE or DELETE operations happening on this table, hence minimal fragmentation).
However since all the columns are foreign keys, if you ever change the PRIMARY KEY of any entity in another table, it will take some time to traverse all rows in the
offerordertable and change the ID for all the occurrences. (I am not sure of this phenomenon, please correct me of I am wrong)Though, this will happen only if the foreign key is set to
ON UPDATE CASCADE. You can avoid this from happening by usingON UPDATE RESTRICT. (You can also set it toSET NULLorNO ACTIONbut that will make the old data useless ?)Read more - How many rows are 'too many' for a MySQL table?, Database table with million of rows and Database that can handle >500 millions rows
An alternative to a cron job can be a
TRIGGERthat runs on every INSERT and keeps storing the set of five items and their order counters for the day and, optionally also the user IDs who ordered each set. At the end of the day you only have to SELECT the user IDs who ordered the set with the lowest count, and empty the counters table.However, remember that using a trigger will add a slight overhead for every inserted order (this might be problematic when the server is under load when a lot of orders are being placed), but the computation at the end of the day will be very fast.
Read more - Mysql trigger/events vs Cronjob
If the "analysis" to be performed is already defined, you might not need to store all the old data. You can calculate the results of the analysis every day and store only the daily results. Further, at the end of every month, you can calculate the analysis results for the entire month (using the daily results stored earlier) and get rid of the daily analysis data for that month. This can also be extended to quarterly, semi-annually, annually, all time etc. However, if the analysis computation method is changed in the future, you might not be left with much old data to recompute.
If you continue using multiple tables as mentioned in the question, you will soon end up with a lot of tables to manage. You might want to go ahead with the single
offerordertable option.Also, the load on the server will increase substantially with increase in restaurants, their outlets and users. Make sure you prepare your database server for scalability (MySQL High Availability and Scalability) or use a solution provided by a cloud services provider.
P.S. I am not an expert in database design, so scrutiny is welcome and appreciated! ?