> While this entity-attribute-value model enables the developer to break out from the structure imposed by an SQL database, it loses out on all the benefits,[1] since all of the work that could be done efficiently by the RDBMS is forced onto the application instead. Queries become much more convoluted,[2] the indexes and query optimizer can no longer work effectively, and data validity constraints are not enforced. Performance and maintainability can be extremely poor.
This is only true if you try to do this for all of your data.
I've used key-value tables loads of times, it's convenient for storing things like global configuration.
What else can you do? Make a table that has every configuration value as a separate column and populate it with only a single row? That seems absurd and worse.
> What else can you do? Make a table that has every configuration value as a separate column and populate it with only a single row?
If the values are singletons, what you're describing is the most efficient. What the author is describing is a surprisingly common anti-pattern where someone has a table with three columns: entity id, property name, property value. Almost like a graph database. Fetching data for one entity (normally one row in a properly built db) is fine, but fetching the data for multiple entities is instantly a mess.
It's worth noting, though, that unless the configuration values are all the same type, you lose type safety with just one column for values. "I'll parse the data as JSON" means your service will fail hard at runtime if someone changes the configuration and uses invalid data.
It wouldn't. If you load configuration and it's the wrong type, now Pydantic is throwing an error. You can't run code if the config is invalid no matter what tools you use: failing validation is still an error.
> work that could be done efficiently by the RDBMS
the RDBMS? You're only using one? Why not spread the work out a little? Even if you think you write all your queries efficiently, nothing stops your teammates from DOS'ing your efficient queries by writing inefficient queries themselves. Last week our team started started piling up write timeouts because another team was modifying one of their tables. Not in their db, in the db.
> Queries become much more convoluted
Please, every ounce of effort invested in ORMs like EF/LINQ is to make code look less like querying and more like plain old object access. For the most part, devs want to work with objects and store objects. If you didn't go the RDBMS route, you wouldn't need EF/LINQs help in decomposing your objects and scattering their parts into separate tables. The least convoluted query possible is to just grab the object you wanted directly.
If the json has a natural key, you can add a json index pointing to it, at least in postgres. But this will probably have worse performance characteristics than adding a separate key column.
Also natural keys suck - if a bugged json comes with repeated key (or no key at all) you will be unable to save it to process it further (well unless you have a separate table to save them). Also some jsons won't be keyed
But storing something with no index at all is probably okay if you don't plan to query much (or if the dataset is tiny)
This is the opposite of what I want. If I'm on my Mac, I don't want menus at the top of the window. I don't want an Office-style ribbon. I don't want buttons that don't look native, or UIs that don't obey the configuration I've set up at the OS level.
Each OS has its own idioms and design patterns. I don't want my experience to be more similar to what someone on Windows sees, I want it to look like the rest of the things on my computer.
Let's not pretend this is what users want. It's what developers want so they don't have to write their UI once for each platform.
Not caring that it feels bad and "wanting a similar experience" are two completely different things. Zero people crave a user experience being how it is because it's similar to the experience on another platform.
"Does it solve my problem" is not "does it look like it looks for other people".
And frankly, I think you would be pretty annoyed to try to use software that's designed to look and work like Windows 8's half-baked Metro UI if you're on Mac or Linux. What you said comes from a place of assuming it's a good baseline.
Nobody forces you to use the applications, you’re free to create native counterparts that fit your scenario (enjoy recreating everything while you’re at it).
> Let's not pretend this is what users want.
It’s what I want, as long as it integrates with native shortcuts. Great examples that I like: VScode, Obsidian. I couldn’t care less if they look native or not.
We are demonstrably not free to reimplement all closed applications. Slack and Discord are two notorious offenders who consider third-party clients a TOS violation. We can argue over the technical feasibility of enforcing this policy, but plenty of people are forced to use these applications as a condition of their employment and are unable to replace them with native counterparts.
We can also argue that employment and computing in general are voluntary activities, simultaneously preserving and significantly weakening your argument.
> While this entity-attribute-value model enables the developer to break out from the structure imposed by an SQL database, it loses out on all the benefits,[1] since all of the work that could be done efficiently by the RDBMS is forced onto the application instead. Queries become much more convoluted,[2] the indexes and query optimizer can no longer work effectively, and data validity constraints are not enforced. Performance and maintainability can be extremely poor.
This is only true if you try to do this for all of your data.
I've used key-value tables loads of times, it's convenient for storing things like global configuration.
What else can you do? Make a table that has every configuration value as a separate column and populate it with only a single row? That seems absurd and worse.
> What else can you do? Make a table that has every configuration value as a separate column and populate it with only a single row?
If the values are singletons, what you're describing is the most efficient. What the author is describing is a surprisingly common anti-pattern where someone has a table with three columns: entity id, property name, property value. Almost like a graph database. Fetching data for one entity (normally one row in a properly built db) is fine, but fetching the data for multiple entities is instantly a mess.
It's worth noting, though, that unless the configuration values are all the same type, you lose type safety with just one column for values. "I'll parse the data as JSON" means your service will fail hard at runtime if someone changes the configuration and uses invalid data.
Personally I’d pass all my JSON-y stuff through Pydantic or similar which would take care of these concerns.
It wouldn't. If you load configuration and it's the wrong type, now Pydantic is throwing an error. You can't run code if the config is invalid no matter what tools you use: failing validation is still an error.
This paragraph rubbed me the wrong way too.
> work that could be done efficiently by the RDBMS the RDBMS? You're only using one? Why not spread the work out a little? Even if you think you write all your queries efficiently, nothing stops your teammates from DOS'ing your efficient queries by writing inefficient queries themselves. Last week our team started started piling up write timeouts because another team was modifying one of their tables. Not in their db, in the db.
> Queries become much more convoluted Please, every ounce of effort invested in ORMs like EF/LINQ is to make code look less like querying and more like plain old object access. For the most part, devs want to work with objects and store objects. If you didn't go the RDBMS route, you wouldn't need EF/LINQs help in decomposing your objects and scattering their parts into separate tables. The least convoluted query possible is to just grab the object you wanted directly.
Make a table with a key and a json column. Use this table as key-value store.
Omit the key and store it in a single json column. The worst of all worlds!
If the json has a natural key, you can add a json index pointing to it, at least in postgres. But this will probably have worse performance characteristics than adding a separate key column.
Also natural keys suck - if a bugged json comes with repeated key (or no key at all) you will be unable to save it to process it further (well unless you have a separate table to save them). Also some jsons won't be keyed
But storing something with no index at all is probably okay if you don't plan to query much (or if the dataset is tiny)
I mean with things like webbrowsers it's so a group of people can have a similar experience regardless of which operating system they are on.
This is the opposite of what I want. If I'm on my Mac, I don't want menus at the top of the window. I don't want an Office-style ribbon. I don't want buttons that don't look native, or UIs that don't obey the configuration I've set up at the OS level.
Each OS has its own idioms and design patterns. I don't want my experience to be more similar to what someone on Windows sees, I want it to look like the rest of the things on my computer.
Let's not pretend this is what users want. It's what developers want so they don't have to write their UI once for each platform.
Developers are users too. If users cared enough not to use cross-platform software because it didn’t look native, then it wouldn’t get built.
Not caring that it feels bad and "wanting a similar experience" are two completely different things. Zero people crave a user experience being how it is because it's similar to the experience on another platform.
> Zero people crave a user experience being how it is because it's similar to the experience on another platform.
Count one here. The main thing for me is: does it solve my problem? Not: is it native/does it look unique, etc.
"Does it solve my problem" is not "does it look like it looks for other people".
And frankly, I think you would be pretty annoyed to try to use software that's designed to look and work like Windows 8's half-baked Metro UI if you're on Mac or Linux. What you said comes from a place of assuming it's a good baseline.
Nobody forces you to use the applications, you’re free to create native counterparts that fit your scenario (enjoy recreating everything while you’re at it).
> Let's not pretend this is what users want.
It’s what I want, as long as it integrates with native shortcuts. Great examples that I like: VScode, Obsidian. I couldn’t care less if they look native or not.
We are demonstrably not free to reimplement all closed applications. Slack and Discord are two notorious offenders who consider third-party clients a TOS violation. We can argue over the technical feasibility of enforcing this policy, but plenty of people are forced to use these applications as a condition of their employment and are unable to replace them with native counterparts.
We can also argue that employment and computing in general are voluntary activities, simultaneously preserving and significantly weakening your argument.
Where did I ever say you’re free to leech off their backend? Reimplement full package.
This is not a popular belief anymore. It used to be mainstream in 2010 but now it's pretty much extinct.
True. Now everything tries to emulate iOS :)