Database design and performance

If you're a new user of AppGini, feel free to ask general usage questions, or look for answers here.
Post Reply
dathanasios
Posts: 26
Joined: 2020-12-26 10:17

Database design and performance

Post by dathanasios » 2023-03-09 21:40

Hello.
For example, someone has developed an invoicing application (like Northwind) and wants to become a service provider (SaaS).
That is, to have customers businesses that in turn have their own customers, who make their moves (purchases, sales, etc.).
Let's say it attracts 50 customers. Each customer has their own 1,000 customers, who make 10,000 transactions per year (a total of 500,000,000 transactions per year or 10,000,000 a year per our customer)
What is the most efficient way to design, maintain, upgrade and respond to databases?
Have one for separate databasefor each client (ie 50 databases) or a central one that will be loaded with 0.5 billion transactions per year?
Best Regards

Thanos

User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1156
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Re: Database design and performance

Post by onoehring » 2023-05-11 06:55

Hi Thanos,

just some thoughts

If you are thinking at this scale there will be more to be considered than database design.
You will probably need to consider which type of database (especially concerning scaling: horizontal vs. vertical).
With 500,000,000 transactions (which will be more than 500,000,000 database actions (CRUD)) this would mean .. if they are equally spaced in time: avery 3.784 seconds. BUT this will probably not be true in real live: There will be low and high times thus server capacity and bandwith would also need to be considered.

As you are here in an AG forum and AG uses a relational database by default (MySQL/MariaDB, and I have not seen anyone here actually using a different relational DBMS ... anyone ..hello?), we simply take your question as one where you are aware of all problems.

Splitting the relational database into "50" different ones will produce other problems e.g. comparing different of your customers, changes in DB design/features would need to be propagated to all databases.
You could lookup MySQL/MariaDB limitations to see if they could deal at all with such amount of data.

Maybe it's worth considering using another database type (NoSQL) which can scale more easily ... but then, AG is the wrong application for you (my 2 cts).

BTW: AFAIK every provider where you can rent a server or such has set limitations to the number of concurrent users/accesses to a database (I ran into problems once). And this limit is by far less than 1,000. So you would need to find another solution here for your application with MySQL/MariaDB backend.

Olaf

Post Reply