Hi,
We want to work with PostgreSQL in our new project. I need your opinion on the best way to create a database.
Description of our Project:
It will be in Client/Server Architecture. Windows Application users will access the server as clients and they are all in different locations. There will be a simple ERP system that will perform CRUD transactions and report them.
We are considering connecting to the Embarcadero Firedac dataset. We can also connect clients with PosgreRestAPI.
Our number of clients can be between 5k-20K.
We have a maximum of 200 tables consisting of invoice, order, customer, bank and stock information. I can create a second Postgre SQL for reporting if necessary.
Question 1 :
Should we install PostgreSQL on Windows server operating system or Linux operating system?
2:
Is it correct to open a field named client_id for each table, for example the customer table, and use this field in CRUD operations to host the same single customer table for all users?
3:
Create a separate table for each User? (result: 5000 users x 200 Tables = 1,000,000 tables)
4:
Create a database per user? (result: 5000 databases)
5:
Is each user a separate schema? (result: 5000 schemas)
Can you share your ideas with me?
Thank you.