18 ms·

There’s nothing stopping you from creating tables and indexes on the fly using SQL but it requires explicit commands. > What would an RDBMS have brought us? I

There’s nothing stopping you from creating tables and indexes on the fly using SQL but it requires explicit commands. > What would an RDBMS have brought us? It would probably work using a forms table, fields table, submissions table, and values table.

scarface74 7y ago
And then what happens when they add a field to the form and the table already has a million rows? What happens when they decide that the numeric field should have strings? It would probably work using a forms table, fields table, submissions table, and values table. I didn’t ask “would it have worked”, I asked “what would have bought us”.
- ajcodez 7y ago
  > And then what happens when they add a field to the form and the table already had a million rows? Maybe I don’t follow. You would insert a row into the fields table. Millions of rows should be fine. > I asked “what would have bought us”. I don’t know. None of this matters really, as long as the service works.
  - scarface74 7y ago
    You said doing a “create table on the fly”. So if they needed to add or modify a field, you would have to do an alter table.
    - ajcodez 7y ago
      I don’t think we’re on the same page here. It’s all good.
    - bunderbunder 7y ago
      Alter table is generally no big deal for any of the use cases that MongoDB is also able to handle. On any good RDBMS, adding a nullable column to an existing table is an O(1) operation. This is the only option that's comparable to what's available in MongoDB, and it has the same performance characteristics. On the great ones, adding a non-nullable column with a default value to an existing table is also an O(1) operation. The good-but-not-great ones, it's also O(N). (As always, you get what you pay for.) For MongoDB, wanting to do this would be unusual, but you would have the option of back-filling every record. It would be an O(N) operation, too. So, for this case, the characteristics of the RDBMS are no worse, and possibly better. Adding a non-nullable column with no default is always O(N), but the fact that you're suggesting a document store as an alternative implies even more strongly that this is not the use case you're trying to cover. That said, if you did do it, it would also be O(N). Converting a numeric column to a string column is always going to be O(N), yes. Whether or not that's the better option is something that's got to be decided in context. Basically, do you want to pay the cost of datatype conversion in one lump sum and then be done with it forevermore, or do you want to pay a small fee for datatype coalescing every time you access that field? There are good reasons to choose both options. However, all too often, the 2nd option is chosen for a very bad reason: Simply assuming that it's zero cost.

scarface74 7y ago

And then what happens when they add a field to the form and the table already has a million rows? What happens when they decide that the numeric field should have strings? It would probably work using a forms table, fields table, submissions table, and values table. I didn’t ask “would it have worked”, I asked “what would have bought us”.

ajcodez 7y ago
> And then what happens when they add a field to the form and the table already had a million rows? Maybe I don’t follow. You would insert a row into the fields table. Millions of rows should be fine. > I asked “what would have bought us”. I don’t know. None of this matters really, as long as the service works.
- scarface74 7y ago
  You said doing a “create table on the fly”. So if they needed to add or modify a field, you would have to do an alter table.
  - ajcodez 7y ago
    I don’t think we’re on the same page here. It’s all good.
  - bunderbunder 7y ago
    Alter table is generally no big deal for any of the use cases that MongoDB is also able to handle. On any good RDBMS, adding a nullable column to an existing table is an O(1) operation. This is the only option that's comparable to what's available in MongoDB, and it has the same performance characteristics. On the great ones, adding a non-nullable column with a default value to an existing table is also an O(1) operation. The good-but-not-great ones, it's also O(N). (As always, you get what you pay for.) For MongoDB, wanting to do this would be unusual, but you would have the option of back-filling every record. It would be an O(N) operation, too. So, for this case, the characteristics of the RDBMS are no worse, and possibly better. Adding a non-nullable column with no default is always O(N), but the fact that you're suggesting a document store as an alternative implies even more strongly that this is not the use case you're trying to cover. That said, if you did do it, it would also be O(N). Converting a numeric column to a string column is always going to be O(N), yes. Whether or not that's the better option is something that's got to be decided in context. Basically, do you want to pay the cost of datatype conversion in one lump sum and then be done with it forevermore, or do you want to pay a small fee for datatype coalescing every time you access that field? There are good reasons to choose both options. However, all too often, the 2nd option is chosen for a very bad reason: Simply assuming that it's zero cost.