undefined | Better HN

0 pointstgarma12349y ago0 comments

I don't agree with that because you really just query the keys for users that have the attribute in question without inserting a ton of nulls and having to work out changes to the attributes when you realize "I wish I would have thought of that column" several iterations later. Spending time up front agonizing about the "overt schema" strikes me as an anti-pattern when you are likely going to pivot later and it's super convenient to just use the same tables as you have been using all along. New users are different in that case but not so totally different so just but the new attributes into the column keyed on "pivot1" or whatever.

0 comments

Matt3o12_9y ago

There is no need to do that. Should not do that if you have arbitrary values in your database. Just create another table with all the keys and values aligned like that:

    id | other_pk | key | value

This of cause would require another query to the database and you might have to iterate through all retrieved rows in your code to build the hash table manually (although your JSON library will do the same).

Im not saying that you should not use JSONB for that (I probably would too for that example), Im just saying there are more ways to design your data storages.

zrail9y ago

This well-trodden pattern is called EAV (entity, attribute, value). It's attractive up to the point where you want to do interesting things with multiple attributes, which is when you start doing multiple nested self joins on the EAV table and performance gets hairy.

For smaller scale auxiliary data it's fine, but so is JSONB.

palmdeezy9y ago

Where can I read more about this?

1 more reply

j / k navigate · click thread line to collapse