2. PHPRunner General questions
  3. [SOLVED] SAAS setup problem
This topic is locked
[SOLVED]

SAAS setup problem

 		11/26/2024 11:53:12 AM
PHPRunner General questions
A
 asawyer13 authorDevClub member

Trying to setup a SAAS app.

I have my SAAS database, which has user info and what database the user is to be pointed to once they get logged in. This is also the primary connection so it will be used to login.

I have my actual database connect, with dynamic database name, etc.

Once the user logs in I have no use for anything in the actual SAAS database.

User can login and it connects to the SAAS database to authenticate.

I can then see all the data in the correct database, and data can be added and changed.

BUT when I try to add child (detail) records, they try adding to the SAAS database which of course those tables done exist.
A
 		asawyer13 authorDevClub member 11/29/2024

I think I'll create a demo app and send to Tech Support. Probably easier to explain what's happening.
C
 		Chris Whitehead 11/30/2024

Have you thought about getting rid of the individual SAAS databases and using a single database for your entire application, this would simplify your setup and resolve the issue. You can add a business_id column to your tables. This column will allow you to associate each record with the correct business.

Here’s how it works:

  • When a user logs in, fetch their associated business_id from the SAAS database.
  • Use this business_id to ensure the user only sees and interacts with records belonging to their business.
  • Every query in your application should include a condition to filter data by business_id.

For example:

  • To fetch records: SELECT * FROM table_name WHERE business_id = ?.
  • When adding new records, make sure to include the business_id field.

This approach eliminates the need for multiple databases while maintaining data separation and security for each business. It also simplifies your codebase and database management.
A
 		asawyer13 authorDevClub member 11/30/2024

Yes I have done that method very often, but in this case eventually the databases could be on different servers so I really need this to be separate databases.

I've worked both methods before so I'm comfortable with both. Most of the time I do one database for smaller apps but this app will be huge.

Thanks for your input though.

Alan
C
 		Chris Whitehead 11/30/2024

I see now why you need to multiple databases.

I think what I'd do is in the child add/edit before save event is get the values, store them in the dynamic database, then return false from those events so the save to the SAAS db doesn't get processed. You might need a bit of Javascript to refresh the page after. I've not tried this but it's where I'd start with it.
A
 		asawyer13 authorDevClub member 11/30/2024

As far as I know you have to have the SAAS database be the primary connection so that the user can login. Once that's done, it seems to me that I should be able to switch the connection to the specific database and everything from that point forward should use that connection. I think that would work perfectly if that was how it worked.