Database design for group of apps/modules aka ERP

[SkrOYC]

We started a good chatting on this other topic and I decided to make a new one more specific.

A happy statement Questions

I’m creating something similar on our company but in another area. I have 2 apps running full, a third is half baked and the fourth is just an idea that I’m now trying to make. I have faced the moment to deside if I should have just one DB with a lot of tables or different ones. How did you managed to move all the info of your apps? It’s just one worbook/database? My fourth big app (for this ERP) is going to share a lot of info from my biggest app, so I started thinking about this. BTW, @A…

My apps are the following:

1. Operations and Logistics (Biggest one).
   This is being driven with around 40 tables of different sizes and purposes. The main one, where everything is a children, grandchildren and more is containing a key concept called “Shipment”. This one is for each container that we dispached to other countries.
2. Quality Control (To be made).
   This should use some of the data that was added to the DB from the Operations and Logistics one. 11 tables to be exact and at least 3 new ones should be created.
3. Finance (Half baked).
   This is on a similar pattern that the 2nd but with less tables being reused. I haven’t checked as exactly as the 2nd but I think that around 8 tables from the first app are going to be used for this one.
4. Employee expenses (Done and one of the smallest of them).
   This is independent from any other app.
5. ERP Launcher (Done, the smallest).
   Similar to the 4th, this is independent and just serves as a launcher, it has 2 tables, one with the apps and another with the users to control which apps are available to each one.

The key points of the conversation that I wrote in the other topic are the following:

I need the convenience of having all in just one place. I would love to have every table for every app (considering the fact that we will have around 10 tables shared between apps) on a single workbook.

1. Are there really any performance issues?
2. Have you done it?
3. What’s your opinnion/suggestion around the “multiple apps, some tables shared” stuff?

Also, the shared tables mentioned on the 2nd and 3rd app just take input from the 1st one, on the 2nd and 3rd will be read only info

Tagging some smart people
@WillowMobileSystems @Marc_Dillon @Aurelien @Steve @MultiTech_Visions @Suvrutt_Gurjar

[Rifad]

Here is an example of app UI Performance with google sheets as database and around 10000 rows in customers and 2-3 related tables with not many complex formulas.

[Rifad]

Here is another example of the really simple app but 5-6 tables connected to the parent table. Here the sync time just takes approx. 5-6 seconds. But UI is very slow and at times we cannot even use it. These applications are used by employees in organizations. Most of them won’t have the latest iPhone or high-end devices with 100% battery and connectivity all the time. So you may consider these because when I contacted AppSheet support they advised me to have a good battery and good connectivity and try a different device.

[SkrOYC]

Have you checked Cache on AppSheet’s app info? It’s under the settings of the device

[Rifad]

Done everything including factory resetting my phone and reinstalling it.

[SkrOYC]

Well, in order to retake the original idea from the OP, has anyone some suggestions?

[bonameblisto]

App Component Reuse Solution Feature Requests

1. Summary We would like to take a subset (Data, UX and Behavior) of an App and be able to reuse this subset into another existing App quickly and reliably. (See example in figures 1 and 2). We propose a solution which is much faster and safer than replicating each single change by hand. (See Figures 3 and 4). The solution is intentionally simple but it is powerful, flexible and fairly easy to implement. There is no hidden magic, the App Author is in control of every step. [export01a] [i…

[SkrOYC]

It’s not really what I need but thanks @bonameblisto

[SkrOYC]

@MultiTech_Visions Can you take a look and provide some feedback? I must have merged everything this past weekend but I don’t want to mess this up

[MultiTech]

My SOP is the following:

• Put everything in a single workbook (if I can get away with it)
• Any tables that will accumulate 10k records per year could be moved into their own workbook
  • I’ll usually put all of these in their own workbook, so all the “big tables” are in one place
  • If they grow beyond Gsheets (20k records or more (per table)), then I’ll move those into a SQL
• Build everything inside a single app
  • The problem (IMHO) with the methods pushed by the platform (build a bunch of interconnected small apps), is that they require you to “re build” everything from one app into another
    • There’s no easy way to copy things from app to app.
  • So building everything in one, then conforming how that looks/works based on who is using the system, is much “easier” on the developer
    • Especially with ongoing updates after deployment, because I only have to do the updates once - in the app everyone’s using - instead of having to repeat those updates per app that needs those updates.
      • *You can usually get away with not having to 100% copy all the table settings from one app to another; but if you’re adding a column, changing the structure, that sort of thing… you have to do that in every app connected to that table.

---

Of course, building everything in one app depends on you building the system appropriately

• If you’ve got SELECT() statements or LOOKUP()s all over the place (inside VCs, show ifs, display names, etc.), then the app will run really slow

It’s all about managing the level of “virtualization” in your app.

• Everything that needs to be calculated takes time, so if you can reduce the calculations needed for anything… you’re winning the race.

---

1 final note… there’s no perfect solution - no “one size fits all”

• There are plenty of cases where building everything in one actually isn’t the best option.

No matter how well you design your app, how efficient your formulas, how… blah blah blah… if you’ve got 10 tables, a bunch of slices, some views that are User_Role dependent, along with a bunch of actions the same way…

• that’s a bunch of stuff for the app to “build” on-the-fly
  • Having a single table, with one view, with no conditional formulas… will load much faster.

So it’s a matter of weighing the pros/cons of each build methodology against the needs of the project

---

Future Projects

One of my recent projects is a sample app of how (not) to build an app

• Basically I asked myself: What’s the worst way to do whatever I was doing when building the app, then I would do that! (^_^)

I hope to have something with that out in the next month or so. (Time is extremely tight right now)

[WillieDonaldson]

From what you've laid out, it sounds like you've got quite the complex setup with different apps/modules sharing tables.Now, about your concern about keeping everything in one place, I get where you're coming from. It's all about convenience, right? But when it comes to performance, cramming all those tables into a single workbook might cause some headaches down the road.I haven't personally tackled a setup exactly like yours, but I've heard some buzz about Cloud ERP for retailers. It's designed to handle diverse data needs across multiple modules, so it might be worth checking out. Plus, it could help alleviate any potential performance issues you're worried about.