Hacker News new | ask | show | jobs
by anton_gogolev 2385 days ago
The whole "...we don’t want duplicated subscriber lists" is a very arbitrary requirement.

What's more, all this could have been avoided if a proper relational database structure (not even denormalized, GordonS) was there in the first place.
2 comments
cogman10 2385 days ago
Agreed. This is a poor schema made worse.

Querying the json is why this is slow. Pull that garbage out of the json and into a couple of tables and you'd be much faster.

link
Izkata 2385 days ago
> Pull that garbage out of the json and into a couple of tables and you'd be much faster.

It looks to me like a user-generated search query, not normalized data. A blob column makes perfect sense if this is the case, and pulling it out into other tables/columns would significantly overcomplicate everything else.

link
cogman10 2385 days ago
More complicated than a custom hashing scheme and required ordering?

I don't buy that. Tables aren't complex.

link
Izkata 2385 days ago
Tables aren't complex, but this particular use can get there. Off the top of my head:

1) Complexity inherent in search criteria: Multiple values per field and whether it's AND or OR. (This can go much much further, as ours does, but I'm limiting it here to what's in the blog post)

2) Centralization of criteria: Multiple tables pretty much means a table per field, which are only related by foreign keys back to the central table.

3) Following on from 2, since there's no central location to identify all the relevant tables, it's much easier to miss one in a JOIN.

4) Following on from 1: Serialization/deserialization, for creating new entries and populating the submission form for an "edit" mode. (At minimum you'll have to reconstruct the multiple-values-per-field situation from the tables)

5) Following on from 3 and 4, alternate uses such as adding a search description derived from the criteria.

There's easy ways around some of this, such as in (4) forcing all the app code to go through the same serialization/deserialization (which sidesteps most of the others), but that's no guarantee that there won't be stray code accessing the tables directly for another reason (hitting on (3)), and almost completely erases the benefit of fitting the criteria into a table relation. It's essentially the same as just using a blob field, except a lot more complicated.

Sometimes blob fields really are the right choice, even in a relational database.

link
sundayedition 2385 days ago
Agreed, but it's pretty clear this was a legacy app that for reasons unknown, wasn't worth a rewrite.
link