Important: Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

Just a general question about db and an opinion request.



  • Hi!.
    Excuse me if this is not a direct Qt question.
    I'm a bit confused about how to manage my client (developed in Qt Widget of course)

    In my country there are 16 regions, each region has 15 modules, each module is an instance of my program, each module manipulates 7 tables... so as you can see, there more than 1000 tables simultaneously being operated in DB because each module has its own registers, but it need to be connected with other instances because the modules need to talk with each other making queries and sharing data...

    My question is, is a practical way to generate over 1000 tables on the server or there should be another better solution to handle this?

    Thanks!



  • Your tables could have a "client_id" field. Then you can do queries from each client and can also get results from other clients.



  • ok!... but still do i need to use more than 1000 tables or is there any other model i could follow?

    thanks


  • Lifetime Qt Champion

    Hi,

    Sure there is: you should take the time to design the database properly.

    @ollarch already suggested a good starting point.

    Without knowing more, here is a starting point.

    First point you are are saying that you have 16 regions, then you have one table that identify these regions.

    Then you have 15 modules per region, so it means you need one table to identify these modules and associate them with the region they belong to (check foreign key).

    These modules all handle the same 7 tables, so each entry of these tables should have a field linked to the module that inserted the data (again, foreign key).

    There you have a base of 9 tables instead of 1000. There might be a need for some more but without knowing what this is all about, there's no way to tell.

    There are several application that proposes to design databases, you should check them.



  • Is more clear to me now.. so basically there is a big table let say "contacts" that contains all the modules registers for contacts with independent ID... something like this:

    Contacts Table

    Region_ID | Name | Address | Telephone | Email

      0                Alpha         Red St           9483827      alpha@domain.com
      0                Alpha5       Blue St           9362625       blue@domain.com
      5                Epsylon      Star St           5433272       star@domain.com
    ....and so
    

    All records from everywhere in the same table using respective Region_ID
    That has a lot of sense!
    Thanks.


  • Lifetime Qt Champion

    @U7Development
    Hi
    You are actually using Database normalization
    https://www.w3schools.in/dbms/database-normalization/
    So we try to steer you towards the second normal form which
    uses keys to bind the tables together and reduces duplicated data
    which really can save lots of space if you have many rows.
    Also makes updates easier and consistent.

    Don't take it must do thing, but merely browse over it and read it
    as a guideline on how to structure the master tables and the support tables.

    Also, for the database system you use, make sure to check how its indexes work.
    Often a DBMS can index keys columns and make lookup much, much faster.


Log in to reply