Which design patterns should I use for storing in BD, managing and displaying dynamic typed data?

I often have to store/display/manage data which type isn't determined and can be changed by user, by external data providers or by whatever at runtime.

For example: I'm gathering some data about work that is completed (say it's sort of report which customer wants to see online). Different kinds of job have their own set of data fields, some of them can be stored in one common field (like a date when it was done, cost, person who is responsible for it, and so on), some of them can't (like distance for transporting service, or maybe type of some materials used during this work, which doesnt suit all kinds of jobs, just one particular one).

So, what approaches I tried:

1. the worst one (imho) is to use only one table and to create dedicated field for each type of data. The fastest one to implement, but increases amount of columns drastically (especially if I create new ones dynamically while importing new data via API). Can be used only if we are 200% sure that the project will never ever grow.

2. acceptable one is to store the most commonly used data in main table (like cost, date and so on) and to make table like (main_record_id, field_type, field_value) which stores all other fields.

but the question now is how to manage it better in PHP.

• approach I often use for small applications - is just to have some const-set or enum to define type, and a bunch of case-like logic that validates, stores, displays and elseway-uses the value according to particular type. it is fast to create and quite clear to read while project is quite small.

• approach I tried to use as well is to define a contract and then create a separate class for each data type that incapsulates all the logic (validation, storing, even displaying sometimes) connected to this type. seems a bit more complicated at startup, but it works, and should (I hope) help me to avoid over-complicating my app in future... BUT! It seems to break the single-responsibility rule, and, which makes me worry way more, I feel that it can be affected by fragile-abstract problem, and a tiny error at the beginning may cause huge problems in future.

So, my question is: what approach would you use in such circumstances? which pattern is practically proved to be reliable when your project grows more than it was expected?
What patterns sould I consider to learn to better see possible ways to be able to make a better choice?

Overall, this seems like asking for a waterfall in the desert - for data to be useful, its properties need to be known (such as data type). I recommend defining data models around the problem at hand and manage expectations with your customer.

Assuming you do really need to handle any data any time, there's a lot to consider but sounds like you may be looking for a modified EAV model (Entity-Attribute-Value).

With the addition of the supported_data_types table, you can extend support for other data types. Generally, the data types should be primitive only as supported by the data store (string, integer, binary, date, etc). Data structures should be represented by entities with attributes.

All data structures could live within the EAV model. This can be enormously complicated especially with nested structures (e.g. a Job entity contains a Job_Result entity, etc).

The complexity can be slightly mitigated by adding new tables specific to the task at hand: a Job entity is represented in the EAV while the Job_Result is a new table.

Using multiple EAV models could help as well assuming the nested structures are also unstructured.

Caution: The more EAV is used, the more complex the design and implementation. Avoid needing to do this at all cost

An alternative is to use JSON or a NoSQL database. This increases the difficulty of querying and indexing, and you still have the problem of having a single junkyard of data.

Other considerations:

• Remember all of the SOLID principles when designing a system and understand related design patterns that support them. For example, it seems like you may be running into a problem around the Open-Closed Principle. (Robert C. Martin has more info).

• Determine a core set of use cases that can be used to design a system that can handle current and future requirements. Design around volatility. (Juval Lowy has more info).

• Consider extracting concrete use cases as plugins that the application can use. Thinking of an IDE for example: A core use case is performing operations on one or more code files. Plugins can add support for code file types, the editing experience, and what operations can be performed with the files.