I am developing an app for a client using retool. Their app will help manage data in three sections.
These sections are upcoming sales, current sales, and a sales archive. I need to be able to move data from upcoming, to current when a sale goes live, and then to archive when the sale is finished. This is a constant and continuous cycle throughout the year.
As a point of reference their latest sale had 308 items. This is an average number of sale items but they have had sales with more. On average they have 4-5 sales per year.
I originally considered separate tables for each and then move the data from one table to another. I am however worried about losing their information if something goes wrong when the data is moved. I am now considering one large table that will contain everything and I will use a status field to help organize. When their upcoming sale becomes their current sale, I can switch the status from upcoming to current, and when that sale is over, I can switch the status to archive from current.
I am assuming I can reference the status if I need to interact with the data.
Both options are viable and have their strengths and weaknesses.
Is all of their current data in supabase?
If you keep all the data in a single table, you don't need to worry about losing data via deletions, but this table may be hard to scale as it grows, you should definitely optimize tables with indexing!
If the 'status' is a value in a column, you will definitely be able to access and reference this via queries from Retool.
Having one table for each stage/status would be more efficient and performative as the tables would be smaller. You would just have to make sure the data has all been copied over to the next stage's table before running any deletions.
Often times, multiple DB tables are used so that if something goes wrong with the main production DB/table there is a backup duplicate. Which is good for safety but uses up for data storage and will need to be 'synced' up after successful changes are made.
My client will be setting up and populating supabase for the first time. Their current setup is outdated, and data is scattered over dozens of tables. The idea of a single table is very attractive and I am glad to hear that the one big table concept can work. Thanks again for your reply.
