Doubt when designing a database


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

  • A single database where records of all physicians are stored in the same tables (agenda table, patients, box); or;
  • Each doctor will have his database, and everything would be separate, each with his schedule, patient chart, box, etc .;

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

    Maybe it's a simple question for many, but I do not have much experience in this area.

  • asked by anonymous 10.08.2018 / 16:12

    1 answer


    In essence it does not make any difference to performance, there will be some, but it will not be significant. And it depends on the load.

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

    The second tends for more information in the memory since everything is separated and not used often, so the cache starts working against and not in favor, and there will be occasional losses.

    Which is better you will not know in advance or know your toric scenario, only the actual use will indicate what works best, and may change according to a change of pattern, until tests can fail because it involves a lot of variable, but you will hardly see big difference.

    Do according to the real need, according to requirements, and worry less about the architecture, just leave it easy to change if one day needs it, but it will not be because of the performance. If you have performance problems it is because you have many other things that are much more wrong, for example not having an index where you need it, or searching unnecessarily.

    All doctors doing CRUD will not tingle because it will be 99.99% of the time with the screen stopped without any operation in the database. And what would all the doctors be? All of Brazil at the same time? Then it may be a problem and it will require heavy engineering. All from a clinic? It has almost zero impact. Is it something important for a hospital? Better hire an experienced engineer to take care of this.

    10.08.2018 / 16:21