What archetecture to choose. lazarus / 16-Databases ---------------------------------------------------------------------- BSaidus · 2021-03-23 17:16 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 · 2021-03-23 18:17 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 · 2021-03-23 19:03 Quote from: MarkMLl on March 23, 2021, 07:17:53 pmFirebird 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 · 2021-03-23 19:09 Quote from: dsiders on March 23, 2021, 08:03:08 pmI 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 · 2021-03-23 19:10 Quote from: dsiders on March 23, 2021, 08:03:08 pmQuote from: MarkMLl on March 23, 2021, 07:17:53 pmFirebird 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 · 2021-03-23 19:22 Quote from: lucamar on March 23, 2021, 08:10:29 pmDon'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 · 2021-03-23 19:34 Quote from: MarkMLl on March 23, 2021, 08:22:22 pmOi! 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 · 2021-03-23 19:42 :-) :-) :-) 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 · 2021-03-23 21:01 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 applicationThe 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 · 2021-03-23 21:17 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 · 2021-03-24 00:59 Hey BSaidus, Quote from: BSaidus on March 23, 2021, 10:17:19 pmThank you all for your responses, You're more than welcome!! Quote from: BSaidus on March 23, 2021, 10:17:19 pm@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? Quote from: BSaidus on March 23, 2021, 10:17:19 pmHere 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 · 2021-03-24 01:23 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 · 2021-03-24 03:48 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 · 2021-03-24 05:32 Quote from: BSaidus on March 23, 2021, 06:16:51 pmHello 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 · 2021-03-24 05:59 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. ---------------------------------------------------------------------- Rainbow6 · 2021-03-24 07:18 Hi, from my very German/European point of view I would start to develop the architecture from the data upwards. First thing to think about - someone else already pointed out - is: Who owns the data? And the answer is simple - it’s personal data and only the patient owns it. Not the doctors, not the application provider. Second thing - do you have a permanent Internet connection? Or can it be disrupted? Has to doctor to work, if there is no connection? A central data store is always easier to maintain and to secure as a distributed one. And 2-way synchronization/replication is a task best left off to pros and not programmed yourself. Last point from me - REST is NOT always the best solution - dealing with highly sensible data I always would prefer a VPN connection and then you can use every access method you like. Just my 2cents. Regards, Daniel ---------------------------------------------------------------------- PascalDragon · 2021-03-24 08:04 Quote from: gcarreno on March 24, 2021, 01:59:06 amAre 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? mORMot is - despite its name - not only an ORM. It provides various facilities for client/server communication as well. And in the Lazarus field its one of the choices with the broadest coverage. Quote from: Trenatos on March 24, 2021, 02:23:52 amIn 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. I however agree with what Trenatos and the others said. First check the legal implications of you writing and providing such a software. ---------------------------------------------------------------------- BSaidus · 2021-03-24 08:46 Thanks you all for your clarification. Concerning the laws here in Algeria ( north Africa ), frankly I do not know anything. This idea comes to me because we really suffer ( mostly old people ) from, NO centralized data , doctors do not use software for best following the health of patients .... ( so many problems ). Here, some one can visit doctor A today, doctor B next week, and Doctor B do not know what treatment that Doctor A gives so there is a risk that the 2 treatment are similar. Adding to all that, We do not have a good internet connection ( at every moment you can experiment disconnection ) this is why I must keep a cache in local. This initiative is personal to try help patients & doctor and it will be opensource. YES !! I totally agree with legals and law ( that I do not know for the moment ). For connecting to remote server I thinked about using VPN. (but what VPN server supporting for exemple 10K doctors ????  ) I do not know ... I'm lost. Thank you for your advices . ---------------------------------------------------------------------- PierceNg · 2021-03-24 09:05 Quote from: BSaidus on March 24, 2021, 09:46:48 amFor connecting to remote server I thinked about using VPN. (but what VPN server supporting for exemple 10K doctors ????  ) Nah. In your scenario VPN doesn't add much security, but will add to your problems. Put another way, don't let adding a VPN give a false sense of security. If you cross the legal hurdles and proceed to build this thing, treat the architecture the same whether there is or isn't VPN. ---------------------------------------------------------------------- DonAlfredo · 2021-03-24 09:07 QuoteFor connecting to remote server I thinked about using VPN.Its easy with the mORMot. It uses REST. Over https. Using nginx with a (free) certificate. Will be very safe. ---------------------------------------------------------------------- BSaidus · 2021-03-24 13:19 Quote from: DonAlfredo on March 24, 2021, 10:07:11 amQuoteFor connecting to remote server I thinked about using VPN.Its easy with the mORMot. It uses REST. Over https. Using nginx with a (free) certificate. Will be very safe. Do you mean by using nginx, creating web services with mormot ?? ---------------------------------------------------------------------- DonAlfredo · 2021-03-24 13:33 The mORMot will [automagically] create webservices for you. You get them more or less for free ! However, that's not what I meant. To make the communication between client and server safe, you might use https. To prevent a lot of mistakes and configuration problems, the most easy way to do this is to use a kind of proxy. Nginx will be your https frontend (and also load-balancer). And will redirect all https traffic on your server to a standard http port on your server itself (localhost). E.g. from 443 to 80. Or from 8443 to 8080. So, nginx listens on 8443 (https,0.0.0.0) and redirects traffic to 8080 (http,localhost,mormot). Easy ! ---------------------------------------------------------------------- BSaidus · 2021-03-24 19:38 Quote from: DonAlfredo on March 24, 2021, 02:33:15 pmThe mORMot will [automagically] create webservices for you. You get them more or less for free ! However, that's not what I meant. To make the communication between client and server safe, you might use https. To prevent a lot of mistakes and configuration problems, the most easy way to do this is to use a kind of proxy. Nginx will be your https frontend (and also load-balancer). And will redirect all https traffic on your server to a standard http port on your server itself (localhost). E.g. from 443 to 80. Or from 8443 to 8080. So, nginx listens on 8443 (https,0.0.0.0) and redirects traffic to 8080 (http,localhost,mormot). Easy ! Great, I will explore this way and I'll keep in touch ---------------------------------------------------------------------- ASBzone · 2021-03-24 21:34 Quote from: PascalDragon on March 24, 2021, 09:04:04 amQuote from: Trenatos on March 24, 2021, 02:23:52 amIn 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. I however agree with what Trenatos and the others said. First check the legal implications of you writing and providing such a software. While many people have weighed in on this point already, it is important enough that more caution is required here.   Writing an application and maintaining a system that will handle ePHI (electronic protected health info) securely, is no joke.   Development is a hobby for me, but cybersecurity and compliance is how I pay bills. In terms of architecture, you need to include security in the following areas at a minimum: -- Authentication:  how users logon and how you determine that they have done so successfully -- Authorization:  how you determine/control what their application and data rights are after they have authenticated -- Audit:  tracking their usage of the system all the way through -- Encryption:  Full encryption for data in transit and data at rest Feel free to direct message me with info about what your app is supposed to do, and I can help you understand additional considerations such as data management and obtaining/tracking patient consent, as appropriate. ---------------------------------------------------------------------- Gustavo 'Gus' Carreno · 2021-03-25 00:59 Hey PascalDragon, Quote from: PascalDragon on March 24, 2021, 09:04:04 ammORMot is - despite its name - not only an ORM. It provides various facilities for client/server communication as well. And in the Lazarus field its one of the choices with the broadest coverage. Humm, interesting!! I really need to dive into a mORMot tutorial of some kind!! Many thanks for the clarification!! Cheers, Gus ---------------------------------------------------------------------- LeadGuit · 2021-03-25 05:39 I try to make some points as well - less on the architecture as OP intended, but more on the general thing: Making it secure from MitM is not too hard - https and keeping the logic on the server gets you pretty far. Now for some context what I'm about to say: I live in Switzerland - and a couple days ago, a NetSec group tested our main Vaccination platform on security - unfortunately, they found quite large and critical holes. The platform currently is offline and the creators are being looked at legally (not sued yet, but officially investigated). If you are speaking german, security report in full is available here: https://mezdanak.de/wp- content/uploads/2021/03/2021_03_21_Report_meineimpfungen-v1.1.pdf. Yes, those points are mainly due to the web interface, but some things in it are simply bad practice and a no go. The crucial points they found on the platform that they found are: - Vulnerability for Password reset: If a Doctor account isn't authenticated yet by the authority, a Password reset can still work and create access to the system - Activation of doctor accounts without proper check: Doctors had to send their "Health Professional Card HPC"  (some kind of ID Card) and a Diploma in, so a human can verify their legitimacy. Needless to say that these can be forged, and that legally that this "validation" is not enough for this kind of data - Non-Web Issue: Every Doctor could access every patient: Iirc you wrote in your initial post that this would be a requirement for the app - the security researchers put that as critical risk - each doctor can access all private data of a patient (name, address....). The Thing is - if one doctor account get compromised, all patient data is at risk. That is not good. - Non-Web Issue: Generation of a patientID: Well, this one made me laugh for it's simplicity...the patient ID is simply the UNIX timestamp when the person registered! So you would be able to crawl patient Data without any problems...usually you would create a hash or the like... - Auth of doctors account without proper "trust niveau": For some reason, doctors who have registered via a different federated ID mechanism, aren't able to use 2 Factor Authentication - according to ISO/IUC 29115:2017 this has to be the case ("high trust niveau" which 2FA is) - And there were some XSS vulnerabilities too. Now, I know you were talking about distributing the software to doctors directly, and patients wouldn't have access, and there would not be a webinterface in the classic sense. But keep in mind what can happen - in case of a REST service, authentication is needed. Else, if somebody, be it by chance or targeted attack, finds the REST endpoints you use your database is compromised. Same goes for the Application you distribute - Authentication so nobody could get a hands on that software and scrape your database. Also keep an eye out for the ISO/IEC standards for "medical grade software". There could be regulatory requirements at your place - from proper IT security (where is your server? Have you hardened it, and how?) over Risk Management, Life Cycle etcetc. as far as I know, ISO 13485 is one of the ISO lizences/standards that you would have to follow. But I'm sure you will find the better ones for your specific use case. So overall - If you are a software pro and have experience in creating Medical software and are just new to the Webservice aspect - find a NetSec firm or group to work together with for hardening and securing, and pay attention to your countries legal obligations with such interconnected systems. If you are a networking pro and simply have never dealt with medical software, then all the vulnerabilities above should have made you laugh and cry. Make sure to read through the ISO standards and the legal requirements to run software at a doctor. If you not experienced in either - put your idea in a nice form, a nice proposal and maybe work together with a software company who has developer such software, and maybe you can make a deal with them that they develop it and give you some fair money in return for the idea (As a film/TV composer myself, I love royalties  ) I don't want to talk you out of it - but I want to make sure that you know what medical software entails and what can happen. It's not as straightforward as an application for a local SMB or an app for your own use. ---------------------------------------------------------------------- DonAlfredo · 2021-03-25 07:47 That are some very good replies ! With lots of info and considerations when designing a med-system. I know (for sure) that the mORMot is able (and used) to take care of all that is written. But designing this from scratch will be a lot of work and will consume time until bugger-free. Again, you might ask for help on the mORMot forum. However, designing such a system (by a mORMot expert) might need more than just a few free beers. ---------------------------------------------------------------------- Josh · 2021-03-25 10:42 Thought I would add an additional idea, that would be patient oriented. How about creating a Mobile App, that stores only the patients data; with facilities for user to add their own information( ie taking herbal medicine, taken paractetomol ibuprofen etc), add when symptoms occur, how often do you forget about symptoms when you see a doctor . If you could convince the Other IT providers to have a facility that creates a QR code after you have finished your appointment, so you can scan and get the details, results of the visit, you then create your own database of the patients information, the advantage is if your admitted to hospital in an emergency anywhere in the world, you would have access to all your treatment and where and when it was done. ( this way your solely repsonible for only your data). I do not think they would allow you to download scan/xray images etc, as these are the property of the health board. Think of a photo the owner is the photograher not the subject. With this system, you could have a paid service where user can upload a highly encrypted/password protected by (finger print tech) backup of their data, but not mandatory. I think this approach would be far more attainable, and more likely to accepted by other medical apps, as your main point is its only after the users private data and nothing else, that a patient can get if they applied for a freedom of information request ( but you would have to apply to all authorities/companies that hold data). You may have to limit use to phones that have fingerprint technology so as to secure the data on the phone that way a doctor disgnosing you, if your are unconscious, can still get access to your data by using your print. Very handy if your going abroad regularly. I know a totally differnet idea to OP idea, so you may recomend moving to a Seperate thread. ---------------------------------------------------------------------- BSaidus · 2021-03-25 21:06 Thank you all for your recommendations and help. First of all, I understand that the MED is a sensible field that I must know all legislation about that, after that I notice that it will consume many time. @josh Yes I planed a MobileApp for patients with a minimum ( taking randez- vous, get certificate .. ). Thanks you all. ---------------------------------------------------------------------- zamronypj · 2021-03-26 00:51 Quote from: LeadGuit on March 25, 2021, 06:39:36 am - Non-Web Issue: Generation of a patientID: Well, this one made me laugh for it's simplicity...the patient ID is simply the UNIX timestamp when the person registered! So you would be able to crawl patient Data without any problems...usually you would create a hash or the like... I think patientID only needs to be unique. As long as generated patientID is unique, even only using  unix timestamp, it is ok. If you are able to crawl any patient data without problems then it is authentication and authorization problem. proper authentication and authorization mechanism is solution. ---------------------------------------------------------------------- Gustavo 'Gus' Carreno · 2021-03-26 01:35 Hey zamronypj, Quote from: zamronypj on March 26, 2021, 01:51:34 amI think patientID only needs to be unique. As long as generated patientID is unique, even only using  unix timestamp, it is ok. If you are able to crawl any patient data without problems then it is authentication and authorization problem. proper authentication and authorization mechanism is solution. I kinda disagree on this one. Have you heard about the Swiss cheese approach to security/danger mitigating? It's been brought up into our collective attention due to the pandemic and , in this particular case, you layer up simple mitigation methods so that the cons of each layer(holes in the cheese), don't line up. So you use a mask, one layer of cheese, keep social distance, another layer of cheese, you get vaccinated, another layer of cheese, etc, etc... Discarding the, obvious, issue with the unixtime id's and relying solely on other layers of cheese is not a good practice. You should always strive to have as many layers of cheese as you can implement. Sorry for the cheesy subject, y'all Cheers, Gus PS: You can also add a bit of ham layers and probably make a nice ham & cheese sub? ;-P ---------------------------------------------------------------------- ASBzone · 2021-03-26 03:56 Quote from: zamronypj on March 26, 2021, 01:51:34 amI think patientID only needs to be unique. As long as generated patientID is unique, even only using  unix timestamp, it is ok. No, that is not the only consideration.  It should be more than just unique.  It should essentially be random.  Using sort of sequential or easily enumerated value is bad. True, you might be okay for a time if *all* other parts of the authentication and authorization code is flawless, but all it takes is one or two weaknesses in some other part of the code, and vulnerabilities can be chained together for exploitable paydays. Familiarity with OWASP will be helpful for anyone designing systems like this. https://cheatsheetseries.owasp.org/cheatsheets/Insecure_Direct_Object_ Reference_Prevention_Cheat_Sheet.html ---------------------------------------------------------------------- zamronypj · 2021-03-26 04:34 Does your bank give you account number using hash value. I dont think so. ---------------------------------------------------------------------- Gustavo 'Gus' Carreno · 2021-03-26 10:26 Hey zamronypj, Quote from: zamronypj on March 26, 2021, 05:34:50 amDoes your bank give you account number using hash value. I don't think so. The ID field in a database SHOULD differ from the visible account number. The ID field in a database SHOULD differ from your visible National Medical Patient ID number, and have an incorporated checksum, hopefully. The ID field in a database SHOULD differ from you National Card ID number, and have some kind of incorporated checksum like in Portugal. The visible part SHOULD be detached from the hidden part and that's what I'm referring to. And getting back to the Swiss cheese model, that means that if you don't transpire an association between a database ID and the visible part, it's gonna be harder to get at the data. No, it's not 100% secure. But what, in the IT world, is? Again, getting back to the cheesiness, it's all about layer upon layer with the wholes not aligning! Cheers, Gus ---------------------------------------------------------------------- BSaidus · 2021-03-27 11:50 The so difficult problem to this is the uniqueness of the ID ( code )  for patients. ---------------------------------------------------------------------- DonAlfredo · 2021-03-27 12:10 The mORMot again ... ;-) https://synopse.info/files/html/api- 1.18/SynTable.html#TSYNUNIQUEIDENTIFIERGENERATOR I will stop about the mORMot now. ---------------------------------------------------------------------- Gustavo 'Gus' Carreno · 2021-03-27 12:17 Hey DonAlfredo, Quote from: DonAlfredo on March 27, 2021, 01:10:01 pmThe mORMot again ... ;-) https://synopse.info/files/html/api- 1.18/SynTable.html#TSYNUNIQUEIDENTIFIERGENERATOR I will stop about the mORMot now. Please don't. mORMot seams to be the exact piece of software to support all the needs, once legal issues are ironed out Notice that I'm also pressing on the cheese subject Cheers, Gus ---------------------------------------------------------------------- DonAlfredo · 2021-03-27 12:52 Well. A personal view. I will never do database stuff anymore without the mORMot ! A few years ago I was used to do the normal SQL stuff. Coding some logic and GUI in FPC and putting some (business) logic in SQL. Then came the mORMot. The de-coupling and abstraction makes designing AND maintaining a breeze. However, after a very steep learning curve. The use of DTO's (Data Transfer Objects) is like stepping from assembler to pascal. You get (very safe) REST for free. Its proven on high scale (e.g. https://unitybase.info/). Public (published) and private methods can be exposed. And, most important for me: the SQLite3 (static included, no .dll or .so or .dylib needed) engine will encrypt the local database, so you can use offline databases on local hardware like an Android phone. However. We are free to make our choices. That is 100% ok. I will throttle the plugging ... ---------------------------------------------------------------------- Gustavo 'Gus' Carreno · 2021-03-28 03:09 Hey DonAlfredo, After that review I now really need to have a look at mORMot!! You just wetted my appetite immensely!! THANKS!! Cheers, Gus ---------------------------------------------------------------------- BSaidus · 2021-03-28 10:54 Quote from: gcarreno on March 28, 2021, 05:09:19 amHey DonAlfredo, After that review I now really need to have a look at mORMot!! You just wetted my appetite immensely!! THANKS!! Cheers, Gus    yes mORMot is wonderfull framework !