0

I'm working on a web application with constantly changing requirements. The relationships between entities in the database have mostly remained the same but the attributes of each entity type changes a lot.

I'm using Java, JPA, and Postgres. Postgres has a JSON type. Is it a good design to remove all non-key and foreign-key attributes and just put them in a JSON attribute?

FooTable Columns Old Design Long id String foo Boolean fooCheck Bar bar /**Foreign key ref*/

FooTable Columns New Design Long id JSON attributes Bar bar /** Foreign key ref */

The advantages I see of the new design are

  1. Less overhead converting between POJOs and JSON
  2. Don't really have to change schema?
  3. Faster to test new things?

A disadvantage I see is migrating out of date JSON. Are there any potential pitfalls I'm missing of this approach?

Improve this question
4
  • 1
    The biggest pitfall is that every database operation involving non-structural data (keys, etc.) will require a JSON deconstruction on every record. You can pretty much throw performance out the window. Thought experiment: what do you suppose happens the first time you try a JOIN on one of these JSON attributes (if your database system even allows it)?
    – Robert Harvey
    May 24 '18 at 15:37
  • @RobertHarvey I don't expect there to be JOINs on the JSON attributes. I guess it is technically possible to do so though.
    – Daniel
    May 24 '18 at 15:55
  • Then you're kind of missing the whole point of using a database. Databases exist so that users can query them, in ways that you cannot predict ahead of time. Throwing out performant JOINS solely for the convenience of using JSON seems like a bad tradeoff to me.
    – Robert Harvey
    May 24 '18 at 15:58
  • That's fair. Some queries will involve JOINs and there really shouldn't be JOINs on the JSON. However, I think JSON would be a fair use for data that is isolated (which occurs a lot).
    – Daniel
    May 24 '18 at 16:22
1

Found some useful blog posts on the subject

TL;DR It's acceptable to use JSON if the data is isolated. It can be hard to migrate JSON data. JOIN performance is really slow.

Improve this answer
1
  • 1
    Can you summarize the main concern expressed in the content? Links are known to go bad over time.
    – Berin Loritsch
    May 24 '18 at 17:11
1

Putting the attributes into a JSON field is kind of sweeping the problem under the carpet. Conceptually you are still changing schema if you add, remove or change attributes - you still have to change code relying on these attributes and you have to update all queries which rely on these attributes. You just don't have the database to verify you are complying with the schema, so you have much higher risk of introducing bugs and you will suffer a heavy performance penalty when querying those attributes.

If it is a problem changing the schema of the database as often as you want, you should probably look into streamlining the deployment procedure with migrations or similar. Since you have "constantly changing requirements" it would be valuable to invest time in optimizing the deployment of changes to minimize friction.

That said, there are use cases where a JSON field is appropriate, for example if the data is opaque to the backend application and only used by third-party plug-ins, or if you are just logging data which is not read again by the application. But this does not seem to be the case here.

Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.