How do you handle < 1 Schema for a database?

Databases
1

Hello all,

In code, how do you handle < 1 schemas in the same database? Say there us the Public Schema then there is another one Schema2. How to read and write to #2.

Or is that a really bad idea?

I am asking bc I have a db that has all of the tables necessary for one application. I need to either add more tables to suit another app, use another schema OR create another database.

This is for a set of web apps that will be hosted, possibly by Xojo (not sure yet).

Thanks,
Tim

2

What do you mean with smaller than one schemas? And the you say you have multiple schemata. There is only one database schema and your main app may use only a part of it.

3

Beatrix, I think OP got greater than and less than backwards.

Tim, I generally have two functions in my database apps for helping with schemas. A function for HasTable(extends db as Database, sTable as String) as Boolean and a function for TableHasColumn(extends db as Database, sTable as String, sColumn as String) as Boolean.

They’re shortcuts because the framework will only list all tables in the database or all columns on a table, so these functions just help me find what I’m looking for. You might be able to do something similar, and then in your second app that needs additional tables you could look for them and create them if necessary.

Edit: (It’s basically the sample code from here that returns true instead of using MsgBox)

If you find my advice useful, you should check out Lifeboat for deploying your Web Apps. I tend to go out of my way to help my customers with web app questions on the forum :wink:

2 Likes
4

Yes Tim, you are correct.

For lifeboat, I have the trial edition. Once I get further, I am sure I will be using it!
Thank you,
Tim

5

Do the schemas overlap? Ie., does the second app use some of the same data as the first app? And is that data shared? Or does each app need a separate set of data?

6

Hi Tim,
I expect there to be some overlap.

But in light of what appears to be a much higher degree of complexity, I think I will either use the Public schema or create a new database.

Tim

7

I have an app that has 2 databases it can access.
the user chooses at startup if he wants to use the production or the test database
then the app use the choosed database.
quite easy to make, just open two databases connexions, and use the one you need.
in my case, as I use only one database at a time, I open only one database at startup.

on another app, I open two postgres databases, but published on two different tcp ports.
so again I have two properties opened at startup, and I use both during the app
some datas are in one database, and other datas in the other database
invoices and customers are in one database, and accounting data in the other.