Hacker News Reader

Comment by danpalmer | Hacker News Reader

There are always two sides to this: joins are expensive, or joins are cheap.

In reality both are true. If you’re doing complex business logic on low cardinality tables, joins win every time. If you’re doing simple retrievals on high cardinality tables, they’re not.

The trick for scaling is figuring out which your use case is and converting between the two as necessary. Maybe one database for editing and one for publishing/serving.

There are rare cases that are high data complexity and high cardinality. Normally these can actually be pushed into one of the other formats, but when they can’t, sharding can often be a good option.

View on HN

If you’re doing complex business logic on low cardinality tables, joins win every time. If you’re doing simple retrievals on high cardinality tables, they’re not

Interesting, can’t figure out why this is so (not a databases guy). Could you please explain or push me in the right direction?

There's a difference between cardinality when it comes to databases (essentially cardinality in modelling vs cardinality in data).

Cardinality in modeling (relationship cardinality) refers to your relationship type i.e. 1:1, 1:M, M:M

Cardinality w.r.t. data refers to how many unique values a particular column can have. e.g. a Primary Key has high cardinality because every row has a unique value. A boolean flag has low cardinality because it only has two values.

TBH, I'm not 100% sure which one the OP is talking about here because you need to factor in number of joins and indexes etc, but from the perspective on relationship cardinality, you can think of some of the common differences in modelling for OLTP ("real time" databases) and OLAP (data warehouse etc).

The former would generally have smaller tables and more joins, while the latter would usually have relatively massive tables which inherently could imply fewer joins, but this is often not the case. It just depends on your system and use-case.

A bit late, but what I meant was that:

With complex data but not too much of it, going all in on relational modelling makes development much easier, more reliable, and databases are perfectly capable of handling the necessary joins.

With lots of data that isn’t too complex, denormalising a little and not using as many joins, or using a DBMS that doesn’t have joins and denormalising a lot (E.g. Redis, Cassandra, etc) will work well.

Most problems are one or the other, or can be refactored into one or the other.

At a previous job, we went hard on the relational aspect, and it worked well almost all the time. Inventory management, order management, user accounts, internal processes, etc. The main place it broke down was one table (out of ~450) of user content that due to the way the product worked, represented about 50% of our data. Working with that table became very hard. The right move would have been then to remove it and use a more appropriate DBMS, abandoning the heavily relational design for just that part.

Reply on news.ycombinator.com