Thread by @_Codito_

🤔 Dictionaries: should they be in the database? 🤔

Dictionary is set of values that are supported in your application. But how they should be defined? Well, as always, it depends 😉 #PHP #architecture

Let me tell you what I think about it 👇🧵

In general, I believe there are two types of dictionaries:

1) Internal, related to your app's logic
2) UI-managable, related to app instance's data

Let's look about difference between them 🙂

Internal dictionaries are represented in the code. With #PHP 8.1+ it's pretty straightforward - you can use enums 🙂 When you need to represent some state, string-backed enum is the best choice, because it's much better for debugging purposes.

Main advantage of such approach is to be able to enforce type in signatures. Thanks to this you're 100% sure you'll get what you expect (one of available enum values).

With constants it wasn't possible, at least not without additional boilerplate code.

For older PHP versions there are several libraries that emulate enum functionality:

- github.com/myclabs/php-en…
- github.com/thunderer/Plat…

and probably more.

When you don't think about enum from database perspective, but as a value object within your code, it can be used in several scenarios:

- serialisation, e.g. to JSON
- logging, e.g. Logstash
- error reporting, e.g. Sentry

In all those contexts you will find strings more useful.

Of course, there are valid scenarios where int-backed enum makes sense. Good example is Monolog's log level, because int-backed enum allows arithmetic comparisons.

Having int-backed enum as internal dictionary does not make much sense when actual values have real-world meaning, because in all of mentioned contexts you would get non-friendly value, that would require looking at the code or having all dictionaries in your memory 😉

Enum's backing type should be chosen by actual needs from the code's perspective. You should think what those values represent, how those values are used within the code, not how they are persisted.

If persistency is required, and for some performance-wise reason integers would fit better, you always can remap values just before persisting, and right after loading from the database 🙂 This is infrastructure concern, not your app's!

The most important thing about such internal dictionaries is that they're part of the code. It's a closed set of values. If this set changes, it requires code refactoring and deployment. Sometimes it needs database migration, too. But everything is explicit in the code.

On the other hand, there are UI-managed dictionaries. These are sets that can differ between instances - code that handles them is exactly the same, but values that are available are not necessarily the same. Think about it as a generic dictionary 🙂

Such dictionaries can't be defined in the code exactly because of this generic nature. For example when you build blogging platform that can be run on premise, each instance can have its own categories, tags etc. Those are dictionaries that are managed outside of the code.

That kind of dictionaries are most often kept in the database (sometimes in config). From code perspective you model database schema, relations between records and you operate on values provided by the users. You actually build some contract for defining and using such values.

And now the best part: you don't need database representation for internal dictionary 😎 If dictionary is managed only through the code, you don't need model, relations, foreign keys. You just need a column representing current value 🙂

This thread was triggered by @davorminchorov's post, where he asked how he should store enum values:

https://twitter.com/davorminchorov/status/1598615498819653632

Some people suggested that enums (dictionaries) should always be in the database, and I don't agree with it 🤷‍♂️

It was also discussed under @PovilasKorop's tweet some time ago, where he pointed that #PHP's enum examples are not-so-helpful. It led to conversation about how he handles statuses (with additional table and foreign key):

https://twitter.com/PovilasKorop/status/1584878990807994369

I thought it would be good to summarise these discussions and my opinions provided there, so here we are 😁 I hope you will find this thread helpful.

Let me know what you think! You can leave your vote and/or reply - I'm open for discussion 🙂