bb.donnay-software.com

continuation
Your comment please

Hi,

A database for each company seems very logical to me.
In the main database there is a table companies. After selection you can connect to the desired company/database.

In the past I suppose the dbf files of each company were also in separated folders. Each folder is as a separated database. If there is a function used to open files/tables this should know to which database the table belong. With an include file you can easily 'route' dbusearea to a new function as you want. Then this function decides if a table is in the main or company database.

I'm wondering why you should use different tables for each year? I thought there is no limitation in size for the tables? I'm using some archive tables for old data. A customer can decide to move data to these tables. For example: In 2023 they move 2019 to the archive if size is a problem. This way there is a history of 4 years available. If they need older data they can switch to the archive.

Just my idea.

Chris, thanks for your time and your idea.

Yes, my DBF database structure had multiple companies (bookkeeping service) and each company in a separate folder. Therefore, the database (mainfolder) bears the name of each company, and the names for the Schemes (subfolder - business year) and the names for the tables (dbf file) are identical for each company

FOLDER ALL_COMPANIES

ALL_COMPANIES.DBF - list companies (menu)

FOLDER Company1
FOLDER 2023
TABLE1.DBF
TABLE2.DBF
FOLDER 2024
TABLE1.DBF
TABLE2.DBF
FOLDER 2025
TABLE1.DBF
TABLE2.DBF

FOLDER Company2
FOLDER 2023
TABLE1.DBF
TABLE2.DBF
FOLDER 2024
TABLE1.DBF
TABLE2.DBF
FOLDER 2025
TABLE1.DBF
TABLE2.DBF

FOLDER Company3...
FOLDER 2023
TABLE1.DBF
TABLE2.DBF
FOLDER 2024
TABLE1.DBF
TABLE2.DBF
FOLDER 2025
TABLE1.DBF
TABLE2.DBF

In our bookkeeping, each business year is unrelated to the previous and the following and must be separated from them. Therefore, it is logical that the database of the business year in the accounting application should be separated from the database of other business years.

This works very well with both DBFNTX and FOXCDX as well as the ADSDBE server.

I am trying to simulate this structure identically on the posgreSQL server, in order to have minimal changes in the code of my existing DBF application. I am convinced that this was the goal of creating the Alaska Xbase++ UPSIZE technology.

This structure can be set identically on the postgreSQL server without any problems, so that it will now be:

database 'all_companies'
scheme 'public'
table 'all_companies'

Database 'company1'
scheme 2023
table 'table1'
table 'table2'
scheme 2024
table 'table1'
table 'table2'
scheme 2024
table 'table1'
table 'table2'
...
...

And this all works very well under Alaska Xbase++ but only with PGDBE pas-trough-sql.
It cannot work with PGDBE ISAM emulation, because to access company2 data in business year 2024 I can only use pas-trough-sql and not ISAM navigation. That's the only problem. This problem seems small and easily solved. But all is not as it seems.

Here is one of the solutions to a similar problem by postgreSQL professionals:
by Allen from Philadelphia, PA
https://girders.org/postgresql/2022/05/ ... -as-schema

Some of our tables are for one year only aswell. We added the year to the table name. No additional folder structure needed for that, works together with our multi-tenancy-model. The table opening method has a year number as an optional parameter.

Edit: In addition, there is a get-set-function for the year. The table opening method - and other methods - reflect that automatically if a table, which is for one year only, is to be used.

Cris,

I use the same model as you. There is the current database and the archive database. When the last business year is finished and closed, the client can copy data from the current database to the archive database and remove them from the current database. When he wants data from the old years he can access them in the archive database. Both databases are available from the same business application. There is a separate submenu in the application for the archive database. Data in the archive database can be only browsed and printed, not edited.

Furthermore, I raised this multi-tenant model one level up. I added in each DBF as the first field the company id. All DBFs are indexed by company id and date/year, as the first index fields. So, all data for all companies are stored in the same database, both in the current and the archive database. There are only two databases for all companies. As you know and said, there is no limit to the size of DBFs.

The speed and time for processing, browsing and printing data from this global database is the same, regardless of the database size, since I use index-sequential programming model and not filters. Scopes also works fine, since they use indexes. Also, for any database change and administration you have to manage only one database, what is a great benefit. I have been using this model since the old Clipper '87 days and it works great. All DBFs/NTXs are stable and reliable.

This multi-tenant database model can be easy transferred into PGSQL ISAM model. It needs only one schema for all companies.

Having the company ID as a table column and/or having the business year as a table column is elegant and simplifies the work for the service. Database normalization is much easier when having all together. With a new project, I would go that way.
But some authorities in Germany and Europe expect separate databases, depending on what kind of business you run. This way of managing vintages in separate tables or databases also makes it easier to delete entire vintages - even for individual companies. And it is easier to verify and to show (!) the vintages are deleted.

Anyway, many ways lead to rome, as we say in Europe.