I have a table “clients”
I wanted to add an ability for clients to have many “phone_numbers”
Instead of making another “phone_numbers” table and setting up a typical client has_many phone_numbers I decided I wanted to try to just store the phone_numbers directly on the client. So this lead me to learning about embeds_many.
So I did all this work, and in the end it does what I want.
Now I’m just wondering why I did it that way and what did it help/solve vs cause.
Please share your thoughts about when and where you choose to use embed_one/embeds_many.
I would love to get perspective from the community
I realize I may have chosen the wrong direction for the way I want to store phone_numbers on clients, I just lack the knowledge as to why its wrong or possibly right.
Funny you should ask because I did start with just an array, but then I learned that I could do a little more validation if I moved it to a embedded schema, also fyi its just a jsonb in the db.
as a rule of thumb, I try not to use JSONB for such cases. It is not that much harder / more code to simply have a one to many relationship, and then you get all the benefits of the SQL database system, including having a defined schema you can migrate.
JSONB / NoSQL databases are good when you have to store data that you can’t predict / strictly define schema of. For example, external background checks reporting system would return some data in JSON/XML, and you could stick it to JSONB field as it arrived. Then you can interpret it / query it some way.
Or, other valid use case would be when you have some front-end application defining some sort of “document” that it wants to save and restore later on. For example it could be useful in a CMS system.
I do not think it is good idea to use it for relational data, and user -> address / phone number is relational data in my view.
Yep, my jsonb’s are used when the type is not really known, and thus the storage is not really known. Normally I’d prefer a table per type but these are user defined things…
I agree in full. I do have an issue articulating the last part:
I do not think it is good idea to use it for relational data, and user -> address / phone number is relational data in my view.
I have the same gut instinct, but a hard time explaining it. Mainly, because Ive never “worked with” (queried, updated, manipulated, etc) jsonb columns, Ive only stored data in one. Can anyone articulate good reasons not to use jsonb for relational structure?
Im currently trying to convince someone that jsonb columns are not for storing consistently structured, relational elements. Im trying to assert that it will lead to performance issues, and also the general inability to query for the data as expected. All I have are guesses though, cuz using jsonb to store relational data feels like cargo cult programming. What is good about jsonb columns, in terms of storing structured relational data? Why would “traditional” relational tables and columns perform better?
Using jsonb to store a bunch of key-value pairs is usually not that bad in terms of performance. It is a bit slower than a native column, but not prohibitively so. You can even index specific keys in your jsonb column in Postgres. What can very easily kill your performance entirely is the lack of statistics on jsonb columns, this can lead to pathological query plans that are orders of magnitude slower than what you would get from a native column where the database has proper statistics.
The part where it can get really awkward and complicated to query is if you store complicated arrays of stuff in your jsonb column. Postgres is seriously powerful in querying jsonb columns, but the usual relational tools are far better and faster for many cases. Complicated queries involving specific parts of a jsonb column can be difficult or even impossible to write, you have a set of completely different operators and functions for jsonb columns, and you can’t use all the usual tools SQL gives you for them.
There are a few more arguments against using jsonb columns unnecessarily in this blog post.
