Doubt when designing a database

Asked

Viewed 98 times

5

What is the best way to design a database for a web system, for example, this system is for doctors, and every doctor has his or her schedule, patients, cashier, etc. What would be better:

  1. A single database where the records of all physicians are stored in the same tables (schedule table, patients, box); or;

  2. Every doctor have his database, and everything would be separate, each with its schedule, patients, cashier, etc;.

    I know there are powders and cons, I believe that the best way would be the first, for the sake of maintenance. But thinking about the performance, and imagining that on an agenda table with all the doctors doing CRUD on the same table, what would be the performance of that? I would like to know by arguments how best to design this database. And why.

    It may be a very simple question for many, but I don’t have much experience in this area.

database performance modeling software-architecture
  • 1

    To opt for form 2 you have to see what rate your tables are populated. The rate does not seem to be very large, the bank is usually able to handle tables of the order of hundreds of thousands or even millions of records without major problems (provided that the volume of data brought in the queries is not absurd). Form 1.

  • 1

    @Piovezan even in billions you will go from 30 to 32 searches, if much. Actually the worst-case scenario, and very rare, is to go from 1 to 3, because it triples (but tripling over so little search doesn’t matter), what I quoted gives 5% loss.

1 answer

7


In essence the performance makes no difference, there will be some, but it will not be significant. And it depends on the load.

The first may have a minimal performance problem because the index trees may be deeper and may have 1 or 2 more levels to analyze before reaching the desired item, but this is usually derisory.

The second tends to put more information in memory since it is all separate and not used often, so the cache starts working against and not for, and there will be one-off losses.

Which is better you won’t know beforehand nor knowing your theoretical scenario, only real use will indicate what works best, and may change according to a pattern change, until tests may fail because it involves too much variable, but you’ll hardly see much difference.

Do according to the actual need, according to requirements, and worry less about the architecture, just let it easy to change if you ever need to, but it won’t be because of the performance. If you have performance problems it is because there are several other things much more wrong, for example not having index where you need, or searching unnecessarily.

All doctors doing CRUD won’t tickle because it will be 99.99% of the time with the screen stopped without any operation in the database. And what would all doctors be? All of Brazil at the same time? Then maybe it is a problem and will need a heavy engineering. All of a clinic? It has almost zero impact. Will it be something important for a hospital? Better hire an experienced engineer to take care of it.

Browser other questions tagged database performance modeling software-architecture

You are not signed in. Login or sign up in order to post.