4 ms·

Regarding the point about having a relational database: I was of the same opinion, but recently it has been challenged. We were working on a very simple applic

by danwee 4y ago

Regarding the point about having a relational database: I was of the same opinion, but recently it has been challenged. We were working on a very simple application and one of the first requirements was: > User should be able to have a list of skills (e.g., Golang, Java, OOP, etc.). Users can be filtered by list of skills as well (e.g., "give me all the users with the skills "Java" and "OOP" but not ".net") So, the non-relation model fits perfectly (so, we ended up using MongoDB and the `skill` attribute of "User" is just an array). I know it's possible to use, let's say, MySQL and build a couple of tables to achieve the same, but it just "didn't feel right" (e.g., we cannot filter anymore by querying only one table... and if that requirement is needed, we would need to build a view. But the view needs to be updated regulary, and it just feels like yet another stone in the road of achieving our requirements. The document model, on the other hand, felt just right)

awestroke 4y ago
Am I missing something, or could you have achieved the same thing with a relational database using joins? Or even an array-typed column?
- deleted 4y ago
  [deleted]
- 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 ago
    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
  - 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 ago
      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.
      - robertlagrant 4y ago
        I 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 ago
      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.
lgreiv 4y ago
YMMV but with the introduction of JSON/JSONB columns in Postgres, this becomes very easy to achieve without introducing multiple databases. In my experience, at some point relational data will be required in most projects, even if it was not a requirement in the first iterations. Obviously, constraints may apply that make NoSQL a sensible choice in some scenarios.
- sime2009 4y ago
  > In my experience, at some point relational data will be required in most projects I've noticed this too. My leading hypothesis is that projects start with simple data storage requirements which don't require much more than "flat file database + JSON", i.e. NoSQL, document database like Mongo etc. The fast and loose nature of these systems is nice to have at the start of a project too, compared to the work needed do SQL. As the project evolves requirements start to appear which require combining data from 1 or more of these "flat files" or document collections. Then you discover that that your data is relational after all and these new "simple" queries you want are hard to do with your chosen (NoSQL) database. In short: As an application evolves and its data grows, it tends to become more relational.
orthoxerox 4y ago
This doesn't look that hard to model relationally: select distinct user_id from user_skills where skill_id in @1 except select user_id from user_skills where skill_id in @2 The biggest hurdle would probably be the lack of support in your database driver for passing collection-like parameters.
toolslive 4y ago
Off topic, but I think skills are not a binary thing. They have levels. Also skills might be transferable: fe programming languages, if you know language X, language Y is just a small step away (not talking about the eco-system here), and skills might be dependent: (How can you have the skill "java" and not have some level of "OOP" ?) I have yet to see an ontology that fits reality here :(
- mikro2nd 4y ago
  Oh!... Having "Java" but no clue of "OOP"? Seen it any number of times. Devs who've migrated from a C or Cobol background with little/no/hopelessly inadequate OO training in particular. The code is predictably horrible.
onion2k 4y ago
let's say, MySQL There's your mistake. Using Postgres with an Array column you can just search for records where the column contains a value if you define it like "character varying(100)[]", eg SELECT * FROM users WHERE skills && '{"databases", "sql"}';
hibbelig 4y ago
Multiple tables are the way to go with SQL, you'll need multiple tables all over the place. So if multiple tables give you a “wrong” feeling, I argue that the feeling is wrong for the technology. It would be very normal to have tables as follows: * Table "users" with an "id" column and some additional data about the user. * Table "skills" with an "id" column and some additional data about the skill. * Table "user_skill" with columns "user_id" and "skill_id" (and the corresponding foreign key constraints).
- never_inline 4y ago
  I have also done this many times but I think GP has a point. This model needs more code, and a hassle when you're just trying to get something working.
  - quickthrower2 4y ago
    Its a wetware thing. Use relational enough and this isn’t a hassle at all. Like static types are not a hassle if you are used to them. Or docker once used to it. Etc.
  - marcosdumay 4y ago
    > This model needs more code That's not my conclusion at all. This model often requires less code. And less hard-coded data your application has to share around.
pif 4y ago
> we cannot filter anymore by querying only one table I'd not want to work in a team where that is actually considered an issue.
scott_w 4y ago
Serious question: why is "having an extra table" considered such a challenge? I've seen this in a few places and it always raises alarm bells in my head.
- dogleash 4y ago
  Relational databases are one of those technologies where enough people can skate by enough of the time with just some very basic skills. And the skill curve in relational databases isn't offset by experience in other domains. There's a number of technologies like this. They grow an air of mystery and impenetrability. Often unjustifiably so; many people would succeed just fine if they would go in unassuming and expecting to learn. It's why my interns can master things in a summer that some of my full-time people have been half-assing for years.

by danwee 4y ago

awestroke 4y ago

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

deleted 4y ago
[deleted]
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 ago
  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
- 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 ago
    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.
    - robertlagrant 4y ago
      I 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 ago
    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.

lgreiv 4y ago

YMMV but with the introduction of JSON/JSONB columns in Postgres, this becomes very easy to achieve without introducing multiple databases. In my experience, at some point relational data will be required in most projects, even if it was not a requirement in the first iterations. Obviously, constraints may apply that make NoSQL a sensible choice in some scenarios.

sime2009 4y ago
> In my experience, at some point relational data will be required in most projects I've noticed this too. My leading hypothesis is that projects start with simple data storage requirements which don't require much more than "flat file database + JSON", i.e. NoSQL, document database like Mongo etc. The fast and loose nature of these systems is nice to have at the start of a project too, compared to the work needed do SQL. As the project evolves requirements start to appear which require combining data from 1 or more of these "flat files" or document collections. Then you discover that that your data is relational after all and these new "simple" queries you want are hard to do with your chosen (NoSQL) database. In short: As an application evolves and its data grows, it tends to become more relational.

orthoxerox 4y ago

This doesn't look that hard to model relationally: select distinct user_id from user_skills where skill_id in @1 except select user_id from user_skills where skill_id in @2 The biggest hurdle would probably be the lack of support in your database driver for passing collection-like parameters.

toolslive 4y ago

Off topic, but I think skills are not a binary thing. They have levels. Also skills might be transferable: fe programming languages, if you know language X, language Y is just a small step away (not talking about the eco-system here), and skills might be dependent: (How can you have the skill "java" and not have some level of "OOP" ?) I have yet to see an ontology that fits reality here :(

mikro2nd 4y ago
Oh!... Having "Java" but no clue of "OOP"? Seen it any number of times. Devs who've migrated from a C or Cobol background with little/no/hopelessly inadequate OO training in particular. The code is predictably horrible.

onion2k 4y ago

let's say, MySQL There's your mistake. Using Postgres with an Array column you can just search for records where the column contains a value if you define it like "character varying(100)[]", eg SELECT * FROM users WHERE skills && '{"databases", "sql"}';

hibbelig 4y ago

Multiple tables are the way to go with SQL, you'll need multiple tables all over the place. So if multiple tables give you a “wrong” feeling, I argue that the feeling is wrong for the technology. It would be very normal to have tables as follows: * Table "users" with an "id" column and some additional data about the user. * Table "skills" with an "id" column and some additional data about the skill. * Table "user_skill" with columns "user_id" and "skill_id" (and the corresponding foreign key constraints).

never_inline 4y ago
I have also done this many times but I think GP has a point. This model needs more code, and a hassle when you're just trying to get something working.
- quickthrower2 4y ago
  Its a wetware thing. Use relational enough and this isn’t a hassle at all. Like static types are not a hassle if you are used to them. Or docker once used to it. Etc.
- marcosdumay 4y ago
  > This model needs more code That's not my conclusion at all. This model often requires less code. And less hard-coded data your application has to share around.

pif 4y ago

> we cannot filter anymore by querying only one table I'd not want to work in a team where that is actually considered an issue.

scott_w 4y ago

Serious question: why is "having an extra table" considered such a challenge? I've seen this in a few places and it always raises alarm bells in my head.

dogleash 4y ago
Relational databases are one of those technologies where enough people can skate by enough of the time with just some very basic skills. And the skill curve in relational databases isn't offset by experience in other domains. There's a number of technologies like this. They grow an air of mystery and impenetrability. Often unjustifiably so; many people would succeed just fine if they would go in unassuming and expecting to learn. It's why my interns can master things in a summer that some of my full-time people have been half-assing for years.