Saturday 28 October 2017

Data models in DBs and GUIs differ

There can be a huge difference between the data model used in a database, and the data model that the user needs. There can also be multiple data models in the same system, based on a single database data model.

When you learn about databases, the first books about databases have a direct match between what is in the database and what is shown to the user. A typical example is a customer list, and one record in the database is one customer. You would look up one customer at a time, assign a sale to one customer etc. Everything is consistent and nice.

But once you try to support the planning and registration of human actions, things get more complicated. When did your sales activity start? At the first thinking of selling to a customer, when you made the first call, or when you started the current call? A database may register all the activities you made, but what if you planned 2 calls to a customer in week 19, but made 1, which of the 2 calls did you miss to make? Does it matter? Well, not if you ask that way. What if you ask, was the sales person lazy, ill or unorganized? The management wants to know.

The same happens in hospitals. Seen from a legal point of view, a prescription is made by 1 doctor. So, when a doctor prescribes antibiotics, it is a kind of a mini-plan. 3 times a day, the patient must receive this antibiotic drug. When does the plan end? Sometimes, the doctor does not specify that, and another doctor makes the decision to stop. Or, the other doctor changes the dose. Or changes the antibiotic drug. Maybe suspends the drug for 12 hours because of a surgery, and then makes a new prescription to start the antibiotics again. Maybe the plan continues during surgery but is just not complied to. Maybe the prescriptions are entirely stopped and restarted later. To the patient it doesn't matter what the plan is, it only matters what drugs the patient received. But to the doctors and nurses, there is a difference in overview, planning, preparations, accountability etc.

How should this be shown to the user? The doctor needs an overview, that shows all edits of a prescription as a continuation of the same prescription. Legally, it is a replacement of the old prescription with a new prescription, but for an overview, it should be considered to be one. If the drug changes, or the concentration, that does not apply, of course, the doctor then wants to see the new prescription in a separate line. So, 10 prescription records can be come 1-10 lines in the overview. For the nurse, other rules may apply. For instance, some drugs require that the drug infusion pump does not stop, so the new drug has to be started before the old is stopped. So, there is an overlap, and that has to be very clear to the nurse, but not necessarily the doctor.

For some drugs, the total amount of drug given, during the patient's stay, is important. So the totals have to be calculated across prescriptions, even if it has been suspended for days. For statisticians, they want to know the duration of the antibiotic treatment, and sometimes consider the replacement of a drug with another drug as a continuation. Additionally, prescriptions can be continuous (infusion pumps) or repeated (e.g. tablets given at specific hours). This subdivides the data models, in some cases, and not in other cases, into multiple data models.

In other words, the data model differs by the use case, even though there is only one data model for the database. This requires a conversion of data, from the database data model, and this conversion has to be applied in multiple places: Each user interface may have its own conversion, and when copying data to a data warehouse, multiple copies with different conversions may take place, in order to provide data in several ways, for easier analysis. And, once the statistician starts analyzing, more data models are needed, and data may be converted again.

Very often, in simple systems, the database data model will be close to the known use cases. However, if a new use case arrives later, this will cause problems. For instance, if the use case is to register the number of cigarette packages per week for a patient, and this becomes the database data model, you cannot easily change it to cigarettes per day later, as the package size may not be the same for all brands, and the database would lose history if the old values are simply multiplied.

In order to prepare for many use cases, the database data model must therefore sometimes deviate significantly from the user interface, sometimes generalized data structures become a very good thing, e.g. by adding records in generic tables instead of adding fields in domain-specific tables. Editing data in such a database requires a good transaction management and good maintenance of database consistency. Hospital software is full of examples of this. The art is to do this without losing performance and maintainability, and thereby functionality and usability.

Thursday 13 April 2017

TIOBE Index update - top 4 languages are going down

The TIOBE index shows:

  1. Top 4 languages are going down (Java, C, C++, C#)
  2. Delphi is now significantly more than 50% the size of Microsoft C#
  3. JavaScript and Delphi are keeping their share
  4. R is going up, fast

It seems that there is a general move away from mainstream languages, towards other languages like JavaScript, R, PHP etc. R is probably rising because of the need for more data analytics, and large organizations have a tendency not to make paid statistical software available to employees that need it - and even universities that train doctors are now switching to R instead of STATA, SAS, SPSS etc.

For JavaScript, I heard a good recently, which it keeps being so popular: It is one of the few programming languages, that you can use without installing any software on e.g. your company computer.

Will Delphi overtake C# and Microsoft .net? Maybe - Delphi works on mobile phones, but Microsoft .net doesn't.

My personal opinion is, that you should pick the development framework that suits your needs, with regard to productivity, legal issues, tool-supplier support, longevity expectations, deployment options etc.