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.