Hi

For my new application (an accounting application) I'm considering the use case of multiple businesses, and one option is "One business, One database" which I quite like as a concept. I know that a single session can contain multiple databases (the EDBManager does this) but I don't know if there is a maximum number of databases that a single session can reference (it's not in the Appendix B System Capacities).

I'm assuming there is no problem opening multiple databases from a single session in my Delphi app.

Has anyone implemented a setup like this, and if so are there any thoughts on best practices, limitations etc. and does anyone know the limit on databases in single session?

Also, just a thought that came to mind, if the session files on disk get corrupted, is there a way to recreate the session and add the databases back to it from code?

Cheers
Shane

Shane


My first tip would be - Don't. If this is for a group where you'll want to consolidate the accounts then have a company code in the table(s) so you can identify individual companies. If not flip the catalog in use (set the config path in the session) but keep the databases separate. Do not try and run multiple databases of the same structure referring to different entities because I'll guarantee there will be major crew ups at some point.

Roy Lambert

Roy Lambert wrote:
>>
My first tip would be - Don't. If this is for a group where you'll want to consolidate the accounts then have a company code in the table(s) so you can identify individual companies.
<<

Thanks Roy - it's not that kind of situation (the grouping does exist, but I had already planned for that as you suggested), but all the same I get your message

I am still concerned about database size, as it's conceivable there could be more than 100 discrete 'businesses' in the same database, and thousands of journal entries for each. I liked the idea of the user being able to backup each one individually as they finished with it rather than having a massively growing database backup file. The way this problem is currently solved is with a spreadsheet per business, but it's limited (no journals for instance). However, it's easy to deal with a 'company file'.

>> If not flip the catalog in use (set the config path in the session) but keep the databases separate. Do not try and run multiple databases of the same structure referring to different entities because I'll guarantee there will be major crew ups at some point.
<<<

I don't understand what you mean by "flip the catalog", and the implications of that. If that's not a spelling mistake, can you elaborate please?

Shane Sturgeon wrote:

Roy Lambert wrote:
>>
My first tip would be - Don't. If this is for a group where you'll want to consolidate the accounts then have a company code in the table(s) so you can identify individual companies.
<<

Thanks Roy - it's not that kind of situation (the grouping does exist, but I had already planned for that as you suggested), but all the same I get your message

I am still concerned about database size, as it's conceivable there could be more than 100 discrete 'businesses' in the same database, and thousands of journal entries for each. I liked the idea of the user being able to backup each one individually as they finished with it rather than having a massively growing database backup file. The way this problem is currently solved is with a spreadsheet per business, but it's limited (no journals for instance). However, it's easy to deal with a 'company file'.

>> If not flip the catalog in use (set the config path in the session) but keep the databases separate. Do not try and run multiple databases of the same structure referring to different entities because I'll guarantee there will be major crew ups at some point.
<<<

I don't understand what you mean by "flip the catalog", and the implications of that. If that's not a spelling mistake, can you elaborate please?


P.S. I probably should have mentioned, this is not a situation where the database is on a server. It's a local database situation.

On 2/25/2021 2:08 AM, Shane Sturgeon wrote:
> For my new application (an accounting application) I'm considering the use case of multiple businesses, and one option is "One business, One database" which I quite like as a concept. I know that a single session can contain multiple databases (the EDBManager does this) but I don't know if there is a maximum number of databases that a single session can reference (it's not in the Appendix B System Capacities).

You can think of session as more of a "connection" which defines how to
connect and config file and all such.

AFAIK there is no limit on what you can access over a session since you
can really only do one blocking thing at a time on a session (like
running a query or such).


> I'm assuming there is no problem opening multiple databases from a single session in my Delphi app.

No

> Has anyone implemented a setup like this, and if so are there any thoughts on best practices, limitations etc. and does anyone know the limit on databases in single session?

Sorry not here so advice is just generic. I would suggest to also
consider shared config/catalog issues - things like same set of users,
stores, backups etc for all databases.

Depending on your software and use case this might be desirable or a
potential problem - like admin users with global permissions would have
access to other databases etc.

I'd also test out schema update and various backup/restore scenarios -
they should be ok but things like individual database restore of some
old DB backup with different schema that rest today etc.

> Also, just a thought that came to mind, if the session files on disk get corrupted, is there a way to recreate the session and add the databases back to it from code?

Session does not really have any files.

I assume you mean config and AFAIk there is no built in backup for
config yet so i would suggest do back that up externally if you can like
OS backup.

Databases you can backup with catalogue (which you should always do).

Raul

Raul wrote:

<<
You can think of session as more of a "connection" which defines how to
connect and config file and all such.

AFAIK there is no limit on what you can access over a session since you
can really only do one blocking thing at a time on a session (like
running a query or such).>>

Thanks Raul,

If I was to proceed with this, and I'm still trying to find a better option given Roy's warning, my thoughts were that there would be 1 database that had "nice" names (like a recently used file list) for the all the "business databases" that were in the config (I may be getting confused with my terms here) file and the user would then choose one of these to "open" and work on. The initial table would provide the name of the database and the session would then open it. When the user was finished, they would either close the app, or pick another "database" from the list to work on. Unless they wanted to copy some elements from one business to another (like the chart of accounts at setup time) there would only be two databases open at any one time - the "recently used file list" one and the "business" being used.

So if you created a new session in EDB Manager and opened this config file, you would see one database "lets call it "AppState" for example and it would have tables in it for various things like templates that would be accessible when creating a new business, and then lots of other databases (each stored in it's own folder on disk, and with a "user unfriendly name" generated by the app to be unique - could even be a GUID given no one will ever see it) - one per business, and they would all have the same tables etc.

When a user deletes a "business" the app drops the database and deletes the folder.

My question was really "how many of these "business" databases can the session/config contain before it becomes an issue for EDB,

Anyway, that was the concept. which I thought was pretty smart but given Roy's been at this a lot longer than I, I'll ponder alternatives.

Having thought about the EDBManager (which is where I got the idea from) just now, I guess another question would be "the Session/config can obviously handle more than one database at a time because EDBManager does it (and I have several open at the same time) but should I be thinking of EDBManager as an anomaly rather than example of good practice?

Cheers
Shane

Shane


>I don't understand what you mean by "flip the catalog", and the implications of that. If that's not a spelling mistake, can you elaborate please?

Both the EDBEngine and the EDBSession have properties which specify which database is in use. Most programs will set these at start up. Using EDBManager as an example when you start it then edbmgr.ini is used to determine what happens and when you open a session the values from the ini file are used to set up things.  If you stay with having one database open at a time you only need one session and when you "flip the catalog" you close the session, replace the properties and reopen the session. I would definitely make a large visual change when the database is changed. Just to have the best chance of a huser figuring out where they are.

Roy

Raul


>Sorry not here so advice is just generic. I would suggest to also
>consider shared config/catalog issues - things like same set of users,
>stores, backups etc for all databases.

Very good points

Roy

On 2/25/2021 10:53 PM, Shane Sturgeon wrote:


>
> So if you created a new session in EDB Manager and opened this config file, you would see one database "lets call it "AppState" for example and it would have tables in it for various things like templates that would be accessible when creating a new business, and then lots of other databases (each stored in it's own folder on disk, and with a "user unfriendly name" generated by the app to be unique - could even be a GUID given no one will ever see it) - one per business, and they would all have the same tables etc.
>

In your app this would work would work just fine.

In EDB Manager one would still see all databases though. Session simply
points to config which would have all databases defined - one cannot
"hide" those. EDB remembers last used but all of them would be there still.

> My question was really "how many of these "business" databases can the session/config contain before it becomes an issue for EDB,

I don't think there is a limit specified in docs as to the number of
databases in a config (not session) - lot of other things are capped at
4096 (number of tables in database, number of users in cofig, etc) so
maybe use that as upper limit for now.

Note that simply having a database does not itself place any load on the
EDB - it's just some config and files on disk.

> Anyway, that was the concept. which I thought was pretty smart but given Roy's been at this a lot longer than I, I'll ponder alternatives.

I think Roy was talking more about business aspect when doing this - i
think he mentioned account consolidation.

Other things will come into play as well like would you create duplicate
contacts for each database that needs to refer to same customers - in
this model there is no easy way to share common address book and primary
contact record and such as starters.

Not to mention you need to manage the logistics of this in the app - for
example backup/restore should be all databases at the same time.

Most of these issues can be solved in the app of course but jsut
something to consider during design.


> Having thought about the EDBManager (which is where I got the idea from) just now, I guess another question would be "the Session/config can obviously handle more than one database at a time because EDBManager does it (and I have several open at the same time) but should I be thinking of EDBManager as an anomaly rather than example of good practice?

Not really - every modern database supports this general ability. Often
instead of a session it's called a "connection" but once connected you
can manage as many databases as you want.

Session is not something special - most non-trivial apps will have more
than one session (each app thread has to have its own for example).

Raul

Shane

Bit of (I hope) clarification

My suggestion to not follow your initial concepts are not so much business related (although as I said consolidation is easier in one case than another) as user and programmer related.

>Having thought about the EDBManager (which is where I got the idea from) just now, I guess another question would be "the Session/config can obviously handle more than one database at a time because EDBManager does it (and I have several open at the same time) but should I be thinking of EDBManager as an anomaly rather than example of good practice?

I'm glad you brought up EDBManager. If you hunt assiduously through the suggestions newsgroup you'll find a request for an option to change the colour of the UI between sessions. I asked for this because I (and I suspect others but cannot prove it) have forgotten which session / database I was in. Its easily done when you have the same structure and you're using SQL. Different structures can help you spot this as you might be told XYZ does not exist. Now imagine a user forgetting this and happily entering an invoice (you said it was accounting) into company X when it should have been company Y. If you're lucky the error will surface before month / quarter / year end. From a programmer perspective its easier to refer to table SALES rather than DATABASE1.SALES and not notice, and the compiler won't tell you that you didn't have the database prefix on one occurrence of PAYMENTS table - where is that missing £73 (or whatever currency you're using). If you only have one database open at a time that's all gone. If you have only the one database it should be possible to avoid things by making a company code a) not nullable and b) entered for each transaction. Automated test plans have nothing on the ability of the average user to screw it up. And they general;ly do somewhere along the line.

Going back to a comment of Raul's

<<I would suggest to also consider shared config/catalog issues - things like same set of users, stores, backups etc for all databases.>>

I have a vague memory of someone posting an issue with speed with loads of tables or databases (can't remember need memory replacement due to age) and the config (or catalog) file needing exclusive access. Its somewhere in these newsgroups if you want to go hunting or someone can remember.

Mentioned briefly - what are you going to do about security and access? Not mentioned yet - are there common files (eg accounting codes) and what were you thinking about for those?



Roy Lambert