Just a general question about db and an opinion request.

U7Development

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!

ollarch

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

U7Development

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

thanks

SGaist

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.

U7Development

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.

mrjj

@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.