Multi-Tenant Database Design - Tenant-specific Fields

Question

I am working on an application modernization project. We run an application which keeps track of requests for medication for patients of multiple companies.

One common design issue we run into keeping track of various fields that of value for one or few customers instead of most. EX: One specialty medication needs to know the potassium level of the patient before and after medication is administered. (something most medications don't need or want to take into account but it critical for one customer)

I am looking to get some guidance on people's experience with various design methods for this kind of situation. I have found a few different potential implementations and I think they all have merit, but I am probably not thinking about all the considerations and I a probably ignoring Best Practices. I think a given implementation would need to take into account a few attributes:

1. Development effort required for adding new customer specific attributes (both table changes and code changes)
2. Availability for using key RDBMS features (Referential Integrity, Constraints, etc...)
3. Performance
4. Limit clutter on tables for when customers leave/go out of business (EX: I don't want to have to maintain/hold a bunch of columns for a given customer if they leave for whatever reason. Thus resulting in them always holding NULL/BLANK values.)
5. Availability/Complexity in implementing dynamic/user generated/configuration based fields. (assuming the DEV team is able to implement this on the code side) There is a direction to make this new application as configuration based/dynamic as possible. So we limit the need for DEV hours in the future. At some point I think they want to get to the point where a Customer can ask for a field, and as long as it doesn't require business logic, the field can be queued up and deployed in fairly quick fashion.

Lets assume this is the base table set, where Order holds all values with are relevant to all or an overwhelming majority of customer orders.

1 - Entity-Value-Attribute (EVA) or Key-Value Pair (KVP) Design

This particular design is the preferred implementation from my application development team. The idea is we would have a single attribute dictionary table (OrderDetailKey), and then an extension table which holds the value for that key for that object (OrderDetails). (One row for Order 1 - Pre-Potassium Level, one row for Order 1 - Post-Potassium Level, etc...).

Columns then become an entry in the dictionary table and necessary code changes are made on the application side to read/write records to/from the extension table and we are off to the races. Cleanup when a customer leaves is easy, because we pull all the records for that customer from the extension table and delete the keys from the attribute dictionary and they have effectively been removed.

The main downside of this approach is it throws away a lot of the RDBMS features and hurts statistics if we need to do any kind of querying for things by Value. (IE: All Patients with a Pre-Treatment potassium level < 10). There are several answers on DBA SE/SO (Here, Here and Here) as well as an article about how this approach is painful in the long term (Here). It also has some performance concerns since multiple attributes in the same query require multiple JOINs to accommodate. As well as all values are stored as string or you have multiple columns/tables for storing different data types which can make read/write operations more complicated.

2 - Customer Specific Extension Tables

This design chooses to build physical tables, per customer that hold all the customer specific fields. I think this is my preferred implementation as long as we can figure out dynamic field creation/access.

From a performance perspective and the RDBMS features/functions this is just as fine any other option. Removing/Ignoring a customer is easy, because you just DROP/ignore the extension table. The main drawbacks are all Code will require custom logic per customer to include a new table (we could probably get around some or all of the read concerns with views. But all write functions will require custom code per customer, which will have some DEV and QA cost associated with it).

3 - Customer Specific Extension Columns on Base Table

This design chooses to just add columns onto the base table with additional columns as needed.

From a development perspective this is slightly less involved than with Extension table because just the columns are new and we don't have to deal with specific tables per customer. We would also get to leverage most of the RDBMS features and from a performance perspective new columns don't require additional JOINs. Theoretically the tables could eventually get wide enough that we would need build a separate table anyway. And if a customer leaves, we would either leave the columns in place or have to remove them and modify generic CRUD operations to exclude those columns.

4 - Dynamic Table Design

I don't know much about this but I have worked with applications with allow for either admin user or end users to create permanent attributes on the fly. I think some implementations create custom permanent SQL objects on the fly for the code to use. I have not been able to find anything that details how this works but maybe this is an option that is sort of a best of both worlds, once we figure out the code side considerations.