Project: one database or 2 databases

Hi, All

There is an interesting question about a project structure, database usage.

1. Initially the classical project is developed for using the one common database, where the tables exists like: customers (organizations), users (of these customers), objects (of these customers), sensors info of these objects, user rights to access to these objects ... And the system code (scripts) are at the same host and not to be published (!)
2. It means that SQL requests into this single DB always uses JOINs into all these tables, to get and show data that is allowed for each user only, at user's organization, objects...
3. But when the project is near to add new real customer, the customer asks that ... they want to host THEIR sensors data on their VPS-host.
4. It's logical, and looks good for project - the customer wants to share the responsibility for their data.
5. But it means that TWO DATABASES are needed: DB table "customers" (organizations) must be in our project's DB, and all other tables must be ... in the SECOND DB on customer's host.
6. And table "customers" also should contain the fields for "customer's DB IP-host, db-name, username, password" to connect to this second db.

But all developed SQL-requests now are broken, all the system is broken... JOIN's cannot work...
How to be in this situation ?

I'm unable to understand 3 and 5 fully, might be a language barrier situation. But I'll give it a shot anyway, as it seems I have a situation that is somewhat similar.

Pretty simple to solve, actually. Add an extra column to (most of) your tables, which indicate what realm (it could be named kingdom, customerspace, section, or whatever you find simple to understand - I named it realm for me) that row exists in. Then use that when selecting. That's basically it.


Example:

You have an organization with id 100, let's say it's a customer to you. In all their rows in the database, set realm_id to 100. When they log in and use your system, always have this in the selects, and when the create and update, make sure it gets set.

Let's move on to organization 101. Same thing, but with realm_id 101, which means they never see anything related to 100. And when they create their own organizations, the realm_id will be 101, so only they can't see their own organizations.

Sandman said:

unable to understand 3 and 5 fully

Click to expand...

It's trouble core: just customer suddenly asked if possible to store their data on their host. So their data - must be separated from other customers. To separate DB. But we have many customers - we need all their info in OUR db.

User rights are variable, can be set to various objects within the organization, but sensor's data always belongs to the organization.

Sandman said:

realm_id

Click to expand...

It's just variable right of an user to see some object_id info.

If I understand correctly, you are providing a SaaS to many customers where their data are stored in your server and one of the customers want self hosting.
I don't see the complication here. Make a setting page that can edit the domain/IP and port number, database name, credentials, etc. Instead of hardcoded it.

aeric said:

SaaS

Click to expand...

Yes ! And the system code is hosted at our own host (and not intended to be published !), and only the DB of a customer needs to be separated.
It's just a topic to discuss, no any hurry or something, just development structure situation. How such can be processed.

Depends on technical or business point of view.
You need to tell that customer, for self hosting there are more concerns. You will need to maintain different version of database, make another design to the front end, they will need to manage or monitor their database themselves in case of any problem. If they want you to cover them, this is incurring additional charges. Are they okay? Hopefully this customer will change his mind, at least for now.

Question is technical - this situation means that all SQL-requests must be re-done, as i can understand.
Simple JOINs from a single DB are impossible here.

I think no choice if you don't want to lose this customer.

Technically, this is back to your design consideration at the beginning.
I have opposite mindset of you. I build for single user first and avoid making it as a SaaS. I think most companies go for SaaS as it is the most cost saving and easier to manage. Now this situation happens, it is much difficult to "break" the join. Basically, you are creating a new version of the entire project.

Can I look at this from the customer viewpoint? Ok?

#6 and #8 are good points.

Either 1. The customer had someone internally who raised a security question or just a what if about location of the DB (means it's Just a question not a direction of movement)
or 2.
This came up - I was on the board of an organisation which used external service provider for construction and maintenance of A DB. The provider houses the DB on servers Outside the country. Because of the local law on protection of personal information and other security concerns board members were not at all comfortable with this.

Discussion led to it being pointed out that to house the DB internally would mean additional staff in an area that was not part of the core business of the organisation. Which is where #6 becomes relevant for your customer.

That is all about just HOW sensitive are the customer's data for them to ask this question or make the stipulation to go VPS.

It's not, to me, so much about a separated database, but, about how you explain the realities to the customer. Whether you are prepared to take on the new development load yourselves. If not then there may be an amicable separation.

You didn't say whether this is an new customer or an existing one with a new need. One assumes that your revenue stream will gratuitously accept the job of creating a new VPS based software for the customer - well - provided the customer will give you full access to their VPS. I have another organisation where the provider won't give the client access to the server inside their client's premises and owned by the client - I digress.

Maybe - you have a different consideration here - all the technical work is what you do anyway.

Apologies if this is off topic - - -

No, it's exactly not the offtopic, the topic is.
Reasons of the customer are the same, yes: requirement to have the data in the country, local security politics of the server location, strong backup question.... all these can be.

For me the main is the technical question, how to update the project structure and how much rework again

Shift everything in customer DB.

QSerg said:

everything in customer DB

Click to expand...

if Customers.Quantity > 1 then .... ?

What DBMS you are using? MSSQL?
It is possible to join 2 or more tables from 2 or more different remote database servers. They are questions of networking and security.

MySQL

peacemaker said:

MySQL

Click to expand...

Are you using B4J, PHP or other programming languages?

MySQL + PHP

Try search.
There maybe some solutions.

$sql =" SELECT database1.tablename1.fieldname1 AS field1, database2.tablename2.fieldname2 AS field2 FROM database1.tablename1,database2.tablename2";

Click to expand...

Is it really possible ?!?

peacemaker said:

Is it really possible ?!?

Click to expand...

It is possible but you need to allow remote access.

peacemaker said:

... JOIN's cannot work...

Click to expand...

When I need to use SQL with joins from different SQLite databases, I use the "ATTACH DATABASE" command (https://www.sqlitetutorial.net/sqlite-attach-database/).

This allows for complex expressions to work over more than one database..

There might likely be a similar function for MySQL.