Diese Seite mit anderen teilen ...

Informationen zum Thema:
Forum:
WinDev Forum
Beiträge im Thema:
13
Erster Beitrag:
vor 3 Jahren, 2 Monaten
Letzter Beitrag:
vor 3 Jahren, 2 Monaten
Beteiligte Autoren:
Zdenko, Fabrice Harari, Al, RAUL2, Viggo Poulsen, Allard

Database design for SaaS

Startbeitrag von Zdenko am 18.04.2015 10:49

Hi,
I am new in webdev and windev tools. But I am not new in RDBMS and application development. The only problem is that about 25 years has passed since my last 4GL programming. At the moment I am reading the WB tutorial and performing the exercises. I would like to develop an 100% web based application that will run as SaaS. Registration of a new Company I plan to make full under application control, without any help from me or my developers. I would kindly aks for advice how to construct my database, file naming, indexing...etc for SaaS so that data among the companies are not mixed togehter. When my application will be available for the market I expect to have about 800-1000 Companies using the application as SaaS.
I believe that nobody would advice to put the phisical database for each SaaS user (a Company) in a separate sub-folder at the server machine. That would be impossible for the maintenance.

So, the only solution might be that all data for all SaaS users (about 800-1000 Companies) are located in one phisical database, but strictly separated from each other. How to do this ???

In order to avoid data mixing among Companies I believe that especial kind of indexing should be applied. Could you, please give me some advise. In my application I will have about 60-70 files in the database.
Thanks

Antworten:

Hi Zdenko,

I see two solutions for your problem.

1. All records are in one DB, and one field contains the company ID/GUID in each file. All queries, hreadseek or other in YOUR code contains this identifier to avoid mix up. This means that any error in YOUR code will display information from one company to another one...

2. You use a separate DB for each customer. In that case, after login from a common DB (company/user/password), you just change the DB NAME to access it in HFSQL. Once you have passed that step, you can only access this company data, as it is a specific DB...

PRO/CONS:
1. PROS:
- only one DB to backup
- only one DB to reindex in case of problem
CONS:
- any mistake in YOUR code and you are in hot water
- very BIG backup to do
- if there is a problem and you need to reindex, ALL companies stop working
- when you need to move a company data to another server because of overload, it's not easy

2. PROS:
- your code CANNOT display anything from another customer
- each backup is much easier, because it's smaller
- each reindex is independant. In case of problem with one DB, everybody else continues to work
- move of the data between servers is easy
CONS:
- LOTS of backups to do

Basically, most of the pros and cons are based on the maintenance, backup and reindexation. So lets remove the backup part: use a replication system (like my open source wxreplication) to do quasi real time backups on as many servers as needed. It would even be possible that one of the data backups be done by a machine that is local to the customer (your data is always safe on your computer too).

In that case, the only things remaining are potential reindex (rare, true, but it would be better not to block everybody during that time), data move between server, and insulation of data.
Those 3 criteria point toward using separate DBs for each customer.

So I would use solution 2 mixed with a replication system to simplify the maintenance.

Best regards

von Fabrice Harari - am 18.04.2015 14:35
Hi Fabrice

Impressive !!

One question I think need attension: What if you, some day in the future, wants to change one of the files by adding or changing a field. Would you have to do this manually in all 600-800 DB's ?

Best regards
Viggo Poulsen

von Viggo Poulsen - am 18.04.2015 15:14
Thank you very much for you answer Fabrice. But Viggo stated the right question. I would be delightfull to have 1000 users, but what if anything has to be changed. An simple index added to the databasde.
I believe you should write s script able to connect the database, probably one-by-one and perform structure change, and launch reindexing.
Am I on the right way ???

von Zdenko - am 18.04.2015 15:59
Hi,
In addition to my previous post, another option should be taken into account. What if I decide NOT to develop the application as 100% web based, but use Windev instead, and form end user environment either as 2-tier or 3-tier c/s on ONE phisical database. Would your pros and cons options be still the same ?
OK, I know nobody works during reindexing.....

von Zdenko - am 18.04.2015 16:28
Hi Viggo,

considering that I would use HFSQL, I would just need to add a hModifyStructure in the init code of my project... Each DB would be modified automatically the first time it is used...

If I want to do it during down time (if there is such thing), a simple loop on each DB (that we have in the common login file) and a hmodify structure would also do the trick

Best regards

von Fabrice Harari - am 18.04.2015 18:22
Hi Zdenko

if you use windev, then the simplest way is for each user or group of users to have his own DB and use a replication system for transport if/when needed. In that case there would be NO advantage at all in having all the data in once place/one DB.

The saas portion is once again independent, and should be verified either before login each time (if needing internet access to use the system is allowable by users) or can be verified from time to time, with temporary licenses. In fact, I even designed such a system able to work by phone, with a vocal exchange of codes, when users do not have internet access.

Of course, if they DO have internet access, the replication system once again acts as a quasi real time backup, and that function can then be easily part of your SAAS offering.

There are many solutions, of course :-)

Best regards

von Fabrice Harari - am 18.04.2015 18:28
Hello Zdenko

Our SaaS is a multi-tenanted server running MS Windows Server 2008 R2 so of course each client is completely independant.
The apps areWindev apps not Webdev and our clients use Citrix for access. It is early days yet as it has only been running for a few months but there have been no problems.

Regards
Al

von Al - am 18.04.2015 22:34
Hello Zdenko

You could also use windev on the user machine and the db in the cloud. Then you donnot need the expensive citrix.

Iam going to implement this option in the near future. The user wil be able to store info local or in a cloud db.


regards
Allard

von Allard - am 19.04.2015 14:53
Hi Allard,

Yes, I think that might be the best option for my development. As Fabrice said, at the moment the user connects to internet database replication will start, and in a few seconds he/she will be updated.

Thanks.

von Zdenko - am 20.04.2015 06:19
Quote
Al
Hello Zdenko

Our SaaS is a multi-tenanted server running MS Windows Server 2008 R2 so of course each client is completely independant.
The apps areWindev apps not Webdev and our clients use Citrix for access. It is early days yet as it has only been running for a few months but there have been no problems.

Regards
Al

Hello Al.

What do you mean with multi-tenanted server? Have you one server? A farm? A virtual server for each client? I'm just curious...
-----

Don't you find much "resistence", on customers, about using the client software in order to access the system? I'm asking you this because where I live, is almost impossible to propose such scheme of SaaS. They inmediately ask for a web-based information system.

Some time ago, I was looking for a solution to give access to win desktop software. I've found 2 solutions but both of them very expensive: Citrix (known for everybody) and Go Global. The second one is really easy to deploy, instead of Citrix, wich I find complex. It also needs a client in your computer, but the server side is better (imho).

Regards.

von RAUL2 - am 21.04.2015 14:50
Quote
Allard
Hello Zdenko

You could also use windev on the user machine and the db in the cloud. Then you donnot need the expensive citrix.

Iam going to implement this option in the near future. The user wil be able to store info local or in a cloud db.


regards
Allard

Hi Allard.

Which bandwidth (u/d) do you consider nessesary for a WD app running with an underlying remote DB? Have you some tests done with this approach?

Regards.

von RAUL2 - am 21.04.2015 14:55
Hello Raul2

The multi-tennanted server is just a standard MS Windows server 2008 running on a VM at a data centre. We setup a folder per client to hold their data and a separate instance of the Windev software and use the MS Windows security to limit the client users to their folder and sub folders. The clients are aware when they sign up that this is a shared server.
At this stage it is early days but it is working well. As we have several different Windev applications we can mix heavy and light users on the server to get as many sets of clients as possible on the server. If performance degrades we can increase the base server specification or create a new server instance and move clients onto that. The server end is managed for us by the data centre.

This has allowed us to offer our Windev apps over the web without having to redevelop them as web applications and Citrix, although expensive allows the clients access to the Windev app from anywhere and on any device.

Regards
Al

von Al - am 21.04.2015 20:21
Zur Information:
MySnip.de hat keinen Einfluss auf die Inhalte der Beiträge. Bitte kontaktieren Sie den Administrator des Forums bei Problemen oder Löschforderungen über die Kontaktseite.
Falls die Kontaktaufnahme mit dem Administrator des Forums fehlschlägt, kontaktieren Sie uns bitte über die in unserem Impressum angegebenen Daten.