I've often seen a pattern where the database schema devolves into a "classifier" system, although perhaps a combination of EAV (entity attribute value) and OTLT (one true lookup table) would be a more apt descriptor.

Basically, you have tables a bit like these:

  - classifier_groups (e.g. product_classifiers, basket_classifiers)
  - classifiers (e.g. product_types, discount_types, delivery_methods, payment_methods)
  - classifier_values (e.g. regular_product, bid_product, timed_discount, loyalty_discount, postal_delivery, courier_delivery, credit_card, paypal)
  - classifier_value_attributes (e.g. product_name, product_weight, product_size, bid_start_date, bid_start_value, discount_start_date, discount_percentage, ...)
  - classifier_value_attribute_values (e.g. "Some product name", "0.25 kg", "2022.08.10 09:00", 5 EUR)

Though sometimes the approach folds in on itself and the attributes are classifiers/classifier_values themselves, or JSON is used, or more tables inbetween are used when you need to track where a particular usage is needed (e.g. when you define a template and then have an item that is based on the template but with changed data).

So the end result is that instead of 200 tables you have just a few instead, but the problem is that you can't really figure out what is happening with just foreign keys (especially when you are trying to do the reverse - refer from these classifiers to another table, since clearly you can't have a foreign key there and instead you do the EAV thing, like "link_type"/"link_table_name" and "referenced_id") and oftentimes you can't really use the DB because the enums and other data that you need to be able to write queries is actually stored in the application code.

Regardless of what you call it, the thoughts on the approach, or at least its components seem split, even back in the day:

1) https://tonyandrews.blogspot.com/2004/10/otlt-and-eav-two-bi...

2) (PDF) https://novicksoftware.com/wp-content/uploads/2016/09/Entity...

3) https://softwareengineering.stackexchange.com/questions/9312...

I've seen some people swear by it, though personally I really dislike it, since any DB schema visualization tool just turns up a black box and a dozen JOINs across these classifier tables will always be less clear than just referencing tables like products, bid_products, timed_discounts, loyalty_discounts etc. So personally, I'd much prefer the 200 tables with foreign keys between them wherever necessary, with really dumb model bindings in the app instead of a bunch of enums, complex service logic and inevitable orphaned data. And yet, somehow people fear the idea of hundreds of table, but would prefer to shove this functionality into bunches of data instead.

(disclaimer: domain is made up so perhaps not the best example, also a proper term for such a pattern escapes my mind at the moment)