5 ms·
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 mod
by danwee 4y ago
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)
- awestroke 4y agoI 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
- robertlagrant 4y ago> 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.
- adammarples 4y agoThe 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.
- robertlagrant 4y agoI know (except I didn't know I didn't like SQL syntax). That's why I'm saying tables are a good idea.
- adammarples 4y agoAdditionally, 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.
- robertlagrant 4y agoAre you replying to the comment you mean to be replying to? :)
- adammarples 4y agoYes, the one in which you considered FK constraints to be a waste of time
- robertlagrant 4y agoCan you quote where I said that?
- twic 4y agoIn 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.
- simonw 4y agoDid you consider using the MySQL JSON_CONTAINS() function? https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html https://dev.mysql.com/doc/refman/8.0/en/json-search-function...