What archetecture to choose.

[BSaidus]

Hello all
I'm trying to write a medical software ( desktop app ) using lazarus/fpc,
    * The software will be distributed for every doctor ( everywhere where are they )
    * It will work with internet connection.
    * It will use the REST protocol ( I think using mORMot )
    * It will connect to remote centralized REST Server who will manage SQL Database.
    * Doctor must find patient informations even if it is not his doctor.
I Want to know your opinion for this architecture, about performance, scalability
or giving me suggestion if you find wikness.
I thought about using SQLite3 local database for every application distributed but I will
experiment problem of synchronization of data to remote Centralized SQL Database.

  I use :
    * Lazarus 2.0.12 / Fpc 3.2.0
    * mORMot trunk
    * MariaDB for SQL database.

Thanks you in advance.   

[MarkMLl]

I'm not competent to comment on REST etc., but as far as the backend is concerned it might be worth looking at Firebird and PostgreSQL.

Firebird admin is a pain but it's OK for local use. Firebird and PostgreSQL have a useful subset of common SQL, except that their facilities differ when you're trying to set up new users etc.

Their overwhelming advantage over MySQL and derivatives is that they have a notification mechanism, so that if one user changes something other users can be advised instantly.

MarkMLl

[dsiders]

Firebird admin is a pain but it's OK for local use....

I keep reading this statement,  but I've never really understood it. Before I started using Firebird, I used both MS SQL Server and Oracle. Now that was mind-numbing, gnaw you own limb off to escape level of pain. I think Firebird is a snap compared to those two.

[MarkMLl]

I keep reading this statement,  but I've never really understood it. Before I started using Firebird, I used both MS SQL Server and Oracle. Not that was mind-numbing, gnaw you own limb off to escape level of pain. I think Firebird is a snap compared to those two.

I'm not necessarily saying that Firebird's facilities are deficient, but its documentation etc certainly is... or at least was when I looked at some coding to use either Firebird or PostgreSQL as a backend with particular emphasis on creating users and per-user dedicated storage on-the-fly.

But once tables etc. existed, most of the SQL was common. Working from memory that includes times and dates, which are notoriously fickle in SQL.

Obviously for single-user use SQLite must be considered since if it's necessary to pass notifications between programs it can be done by private IPC.

MarkMLl

[lucamar]

Firebird admin is a pain but it's OK for local use....

I keep reading this statement,  but I've never really understood it.

Don't bother, it's a knee-jerk reflex. All databases are a pain to administer; only some of them have been a longer time in production (and more money invested in them) and have more or better admin tools and documentation, which makes it a somewhat lesser pain. But it's always a pain.

That's why there is a specific job post for it: "Database Administrator".

[MarkMLl]

Don't bother, it's a knee-jerk reflex. All databases are a pain to administer; only some of them have been a longer time in production (and more money invested in them) and have more or better admin tools and documentation, which makes it a somewhat lesser pain. But it's always a pain.

That's why there is a specific job post for it: "Database Administrator".

Oi! Who are you calling a jerk? :-)

Well, all I can say is that I'm "telling it as I found it". But I'd emphasise- again- that at least part of the shortcoming was poor... no, /lousy/... documentation. PostgreSQL is far superior in this area.

MarkMLl

[lucamar]

Oi! Who are you calling a jerk? :-)

No, no, knee-jerk! You know, like when the doctor tells you "this won't hurt" and then wacks you with a hammer on the knee? So you respond by (completely unintentionally) kicking him in the face?

And, yeah, Firebird is a little "poor" in the documentation department and I myself also prefer PostgreSQL for medium- to large-scale installations. But you have to admit administering a db of any description is almost always a pain or quickly becomes one, more so if there's no dedicated admin and the job falls on some poor overworked programmer/analyst who is also the systems's admin.

[MarkMLl]

:-) :-) :-)

As I said (twice) though, this was more of a documentation issue than anything else. The important- /very/ important IMO- point is the availability of a notification mechanism in both.

I also get the impression- although I need to be even more careful since it's a very long time since I've tried to use it- that MySQL is very picky about the location and version of libraries, which adds to the support burden.

MarkMLl

[Gustavo 'Gus' Carreno]

Hey all,

If I may...

The poor OP asked a question about an architecture scenario and we devolve into a virtual "My database is bigger than yours, just ask my dad!".

Hey, I'm all for discussions about the length of one's pendatabase, but can we get on track? Thanks guy, I knew you would understand.

@BSaidus:

At a first glance your architecture is quite sane. But have you considered that you have to make 2 applications?

• The client side application
• The server side application

You mentioned mORMot, I've never used mORMot, is it a client side thing or a server side thing, or even both?

If you decide on Lazarus/FPC for the client side, are you gonna do the server also in Lazarus/FPC?
I ask this because there are better suited languages, i.e. PHP(Using a framework of course), for the server side in regards to implement a REST server.

Cheers,
Gus
PS: I still say that mine is the biggest pendatabase of them all!!

[BSaidus]

Thank you all for your responses,
@gcarreno
   Yes, both the client and server side will be in lazarus / fpc.
Here is 2 images illustrating the 2 cases I imagine.

[Gustavo 'Gus' Carreno]

Hey BSaidus,

Thank you all for your responses,

You're more than welcome!!

@gcarreno
   Yes, both the client and server side will be in lazarus / fpc.

Are you sure you want to do a REST server with the tools available under Lazarus?
I'm probably saying this because I've never used mORMot, but that's only the ORM.
What are you planning on using to do the whole Web Framework thing?

Here is 2 images illustrating the 2 cases I imagine.

Please go with image 2, the simple one. Oh, please, please do!!
You would get into so much trouble with the synchronization that you would want to throw yourself off a cliff!!

Cheers,
Gus

[Trenatos]

Before thinking about what language to use to build this product, how much experience do you have in the software medical field? What are your credentials for building something like this?

How much do you know about patient confidentiality laws in your country?

How about storage concerns?

How are you going to protect the data in transit, at rest, from physical access?

What's your backup strategy? Data recovery?

You mention using a local database (This likely breaches HIPAA or equivalent laws in your country) but even if it doesn't, how do you protect it?

How do you track what data is available where and to who?

What regulations do you need to follow? What extra ones SHOULD you follow?

What kind of security is required by law? Do you need governmental approval before any patient data comes near your software?

Any particular certification requirements?

What systems will you need to interface with? Do THEY require you to conduct any particular testing or commit to any particular certification?

This is just stuff off the top of my head. Writing medical software is a Very Big Deal because they deal with so much critical information and medical systems are big targets for hackers.

----

In general, I'm all for the go-for-it approach, learn-on-the-fly. But medical systems are not the place to grit your teeth. There's too much at stake, including peoples lives.

[dbannon]

I am afraid I must line up with Trenatos, its not a topic to be treated lightly.  So, re-mentioning privacy, security and data integrate.  I was involved, as a consultant in building some policy on related matters and I can assure you its big.  Firstly, you would need to decide just who owns the data you have stored, you, the practitioners, the patients ? Good luck with that.

I am sure you are a great programmer, BSaidus but are you also a great laywer ?

But you also need to look at what would be stored in these records, medical imaging already takes up a huge fraction of every thing we store and its growing exponentially. I think its likely that is what the medical staff are going to want stored. And if you store the only copy, then you better make sure its safe .....

Wow, big project !

Davo

[PierceNg]

Hello all
I'm trying to write a medical software ( desktop app ) using lazarus/fpc,
    * The software will be distributed for every doctor ( everywhere where are they )
    * It will work with internet connection.
    * It will use the REST protocol ( I think using mORMot )
    * It will connect to remote centralized REST Server who will manage SQL Database.
    * Doctor must find patient informations even if it is not his doctor.
I Want to know your opinion for this architecture, about performance, scalability
or giving me suggestion if you find wikness.
I thought about using SQLite3 local database for every application distributed but I will
experiment problem of synchronization of data to remote Centralized SQL Database.

  I use :
    * Lazarus 2.0.12 / Fpc 3.2.0
    * mORMot trunk
    * MariaDB for SQL database.

Thanks you in advance.

As a medical application dealing with patient data, information security considerations - confidentiality, integrity and availability - and their legal implications should be paramount.

The 3-tier architecture of a server providing RESTful API is good. You will then have the option of implementing a desktop "thick client" application, or a web browser-based Javascript (maybe transpiled using pas2js) application. With mobile-responsive web browser Javascript (plus a little more work), you get iOS and Android phone and tablet accessibility. There should be almost no data kept locally. If poor network connectivity necessitates caching data locally, think hard about the infosec and legal implications.

A desktop application using a local SQLite database will work well locally. But this has the risk of local SQLite databases being compromised due to poorly secured desktop computers. There is also the problem of synchronizing every desktop's data. Various approaches are possible, e.g. https://github.com/rqlite/rqlite, https://github.com/Expensify/Bedrock, but then you are really using SQLite plus something else.

Don't do local desktop application 2-tier client-server to a central database over the Internet. Just no.

[DonAlfredo]

You might also ask for advice on the mORMot forum itself. Many experienced users will help you.
And yes, the mORMot/SQLite3 combo is a must due to security. The local database MUST be encrypted. And this encryption MUST be auditioned. The mORMot offers the best (free) encryption for SQLite3. And much more.