Hacker News Reader

Comment by awestroke | Hacker News Reader

Am I missing something, or could you have achieved the same thing with a relational database using joins? Or even an array-typed column?

View on HN

That's what I wrote yes. I could also have achieved the same using the file system and plain files, or using a graph database. My point is/was: the document model seemed to us the model that fit the best for our problem: the `skills` attribute is an unbounded list (well, in practice the list was bounded to have at most 50 items) that can be filtered by. So in MongoDB, that's plain simple to implement. In MySQL, though, as you said, you have to come up with two tables (one for `user`, one for `skills`, make sure FK are in place, and then use joins for filtering... doesn't seem to me that this model fits better)

I interpreted your comment as you claiming a view would have been needed.

The list of things required in a relational database does not seem long to me. I could probably come up with an even longer list for what's required when using Mongo.

Also, my point about an array typed column still stands

In a docucument database you model this as a document with a field which is an array. In relational database, you model this as two tables.

In both cases, you simply model according to the philosophy of the database. It is incorrect to think either is more "natural" in some intrinsic sense.

> In MySQL, though, as you said, you have to come up with two tables (one for `user`, one for `skills`, make sure FK are in place, and then use joins for filtering... doesn't seem to me that this model fits better)

Their other option was an array-typed column. As per the article, Postgres' JSONB column type will give you that, as one example.

But the skills table - and many to many linking table - shines as soon as you want to treat skills as first-class concepts in some way, e.g. "only let people pick from this list of skills the skills administrator has approved" or "count how many people have the skill 'Java'". Then tables seem much more natural, as the skills table can model them as concepts in their own right.

The data model fits better. The data is relational and fits neatly into a simple relational table of user_id, skill_id. I think perhaps you just don't perhaps like SQL syntax which is why a join feels weird to you, but what you've described is a 4 or 5 line sql query which anyone can understand. Additionally, you can now easily answer questions like. "How many skills does the average user have?" "Who has the most skills?" "What are the most popular skills?" etc, all likewise in trivially simple sql expressions.

I know (except I didn't know I didn't like SQL syntax). That's why I'm saying tables are a good idea.

Additionally, FK constraints are a good thing, they mean that one of your users doesn't have the "Python" skill while another one has the "Pytthon" skill. You need this - and with SQL it's extremely easy to implement.

Are you replying to the comment you mean to be replying to? :)

Yes, the one in which you considered FK constraints to be a waste of time

Can you quote where I said that?

Did you consider using the MySQL JSON_CONTAINS() function? https://dev.mysql.com/doc/refman/8.0/en/json-search-function...

Reply on news.ycombinator.com