5 ms·

'000s of rows of data in a spreadsheet - sigh. But say 'database' to some people and they hear 'The Devil'. Excel is a glorious tool which welcomes all, the sa

'000s of rows of data in a spreadsheet - sigh. But say 'database' to some people and they hear 'The Devil'. Excel is a glorious tool which welcomes all, the savvy and the unskilled but imaginative newbies alike. There is something about all those little cells that presents an itch everyone wants to scratch, and you just know that for some that scratching is going to produce something akin to a spreadsheet version of gangrenous melanoma. On the other hand, some people are happy to say that the complexity of sophisticated Excel models means they would have been better off built in a code interface. Ha ha ha! Even with the right mix of functional and object-oriented code and suitable documentation and version control applied to code, I respectfully disagree. Data handling capabilities could benefit from skill in all of these tools: error checking (including analytical review); pen & paper; calculators; databases; spreadsheets; math & stat techniques; Word processors (& clear logical explanations). Together, they form a rich menagerie of tools which will probably all be around until we have an AI that follows us around and we just tell it what we want. Actually, even then, it would be good to know them so that we can grasp the underlying logic and frame the concepts leading to the actions that we ask the AI to assist with...

zoomastigophore 5y ago
I think one of the main problem with databases is how do you populate them. I wish there were simple-as-Excel frontends for databases where a normal user could input his data like in preformated Excel table without having to deal with the database mechanics.
- segfaultbuserr 5y ago
  Microsoft Access?
  - mavhc 5y ago
    What we really wish for is a modern replacement for MS Access
    - kitkat_new 5y ago
      modern in what way?
      - mavhc 5y ago
        Faster, better at handling multiple users, smarter, web based. And also free with whatever package you're already paying for :-)
      - IanSanders 5y ago
        Written in javascript
        AryanBeezadhur 5y ago
        You mean MongoDB Compass? https://www.mongodb.com/products/compass https://www.mongodb.com/products/compass
        ComodoHacker 5y ago
        And wrapped in Electron.
      - phoyd 5y ago
        "More Modern" such that it does not contain 8 year old unfixed bugs. https://stackoverflow.com/questions/68034271/how-can-i-get-this-8-year-old-vba-64-bit-compiler-bug-fixed https://stackoverflow.com/questions/68034271/how-can-i-get-t...
        mushi 5y ago
        You know what database doesn’t contain 8 year old unfixed bugs? A database that’s 7 years old.
  - cutler 5y ago
    Filmmaker Pro? Please, no.
- otherme123 5y ago
  Sadly databases are hard because data is hard. I've seen people trying to stuff one-to-many relationships in a single Excel tab duplicating the one side as many times as needed, e.g. Author_A Book_1 Author_A Book_2 Author_A Book_3 Author_B Book_4 Author_B Book_5 Another strategy I've seen is to stuff the many-side into a single cell, splitted with some less used symbol like |, ; or : . I'm curious to find what would they do when they meet a many-to-many relationship. Horrors surfaced when we analyzed the data: unintended duplications because ids and uniqueness checking was not a thing, shifted columns, undocumented column "formats"... In the end we parsed the whole mess into a DB. I don't see how you could implement a frontend for a database simpler than Access. It was as easy as Excel, with relationships. But reasoning a schema is harder than stuffing all things in a 2D table, and querying a multitable is harder than filtering a few columns.
  - zoomastigophore 5y ago
    I agree data is hard. If we could provide a properly designed database which has already defined datatypes but that would be as easy to fill for a standard user as a preformated Excel table that would be great. You're right, Access has this simplicity, I just wish such tools would be available out-of-the-box for other DB engine.
  - chux52 5y ago
    The author/book example is actually the preferred method to format data in Excel. From there you can take the entire table of data and put it in a pivot table and answer most questions pretty quickly. For the cells with multiple values, I agree that is a terrible idea.
  - beckingz 5y ago
    This format is referred to as third normal form (boyce-codd 3NF). Also referred to as "tidy data" in the hadley wickham world.
- Andy_G11 5y ago
  MS Access can be directly linked to a spreadsheet for upload / download, or you can copy and paste data directly or import data files. Other databases (MySQL) also allow for import of CSV data.
  - cutler 5y ago
    MySQL connector for Excel.
- dsr_ 5y ago
  What do we know about most database projects? They need CRUD plus queries. What's the easiest way to generate a CRUD form? HyperCard. We need a ubiquitous HyperCard equivalent that talks to the Real Database(TM) of your choice on the backend -- sqlite, postgresql, mysql/mariadb, Oracle, MS-SQL, whatever -- with easy functions for "get a sequence number", "get a unique identifier cookie" and "do this block atomically". I bet there's a few out there right now.
  - gopher_space 5y ago
    Filemaker is a neat program.
  - CRConrad 5y ago
    Delphi has been around (and better than HyperCard) for twenty-five years. Free Pascal for at least as long, but it has only had the visual component aspect provided by Lazarus for ten or fifteen years.
- AryanBeezadhur 5y ago
  My preferred database is MongoDB, which comes with a GUI called MongoDB Compass that allows you to visually explore your JSON documents with full CRUD functionality. https://www.mongodb.com/products/compass https://www.mongodb.com/products/compass
- ubermonkey 5y ago
  There used to be many such tools, or tools that tried to do these things. We lost them.
- II2II 5y ago
  > I think one of the main problem with databases is how do you populate them. There are several products that will let you populate databases in a table view and easily import CSV files into a database. The problem is with almost everything else. Databases are designed to address a different set of problems. The design of database applications reflects the needs of people who need to maintain a database and make sense in that context. Unfortunately, those design decisions do not make sense in the context of people who use spreadsheets. Doubly unfortunate, a spreadsheet makes a better database than a database makes a better spreadsheet. If anything, the shortcomings of spreadsheets implementation dependent in most cases while the shortcomings of databases are due to the domain they are designed to address. First example: creating a table is more involved than creating a new file and being presented with a blank table. First, a database is created. Then a table is created within that database. Then columns are created with each column, as a minimum, having a type. Only then can the database be populated. Second example: doing a quick calculation is more involved than finding an empty cell and inserting a formula. The only way I can think of doing a calculation within a database involves performing a query. If the calculations are performed within a row, a new column can be created to contain the results. If it makes sense to store a result for each row, but it pulls or consolidates data from other rows, the complexity of the query is going to be interesting. If the calculation involves several rows, there is no obvious place to store the result within the table. There are several ways to deal with that, yet none of them are particularly convenient. If you need to perform a calculation on arbitrarily selected data, well, good luck with performing the query. Oh, and automatic recalculate ... Third example: putting the data into a presentable form is less convenient with databases. It certainly isn't done in the table view since that is just a raw presentation of the data. A form is useful here, but it is a separate step and it isn't going to address all of the functionality found in spreadsheets.
- nocodb 5y ago
  > I wish there were simple-as-Excel frontends for databases This is exactly what we are attempting to solve at nocodb : https://github.com/nocodb/nocodb https://github.com/nocodb/nocodb Which is, nocodb gives you a google-drive like collaborative spreadsheets on your existing databases (MySQL, Postgres etc) And the original problem in article could be countered with nocodb as we keep audit of all changes done to the database.
  - tutuncommon 5y ago
    This looks like a good tool.
- rrrrrrrrrrrryan 5y ago
  Such tools exist, but aren't popular because they're frustrating. Relational databases are much more rigid than flat files. Columns have datatypes which must be enforced, there are foreign keys, some (combination of) columns may have uniqueness constraints, and so on. All this adds up to a bunch of errors when you're trying to input and manipulate data, because what you've entered is violating some rule someplace in the database. It would be awesome if someone could build an interface that gives users the flexibility of Excel, and communicated the issues with the data they're trying to write in a really intuitive way. (e.g. syntax highlighting for data with issues, mouseovers / tooltips, dropdowns & autocompletes for foreign keys, etc)
imwillofficial 5y ago
I found your point to be incomprehensible.
yodelshady 5y ago
Say "database" and IT says "no, unless we spend a year reviewing requirements". This is only a failure story for every software development route that involves procurement, which is every route except Excel. Those routes tracked, within the only timescale that mattered, precisely zero patients. You want to give smart users DB and DVCS tooling? I'm right behind you. Start asking why they don't have those things already.

zoomastigophore 5y ago

I think one of the main problem with databases is how do you populate them. I wish there were simple-as-Excel frontends for databases where a normal user could input his data like in preformated Excel table without having to deal with the database mechanics.

segfaultbuserr 5y ago
Microsoft Access?
- mavhc 5y ago
  What we really wish for is a modern replacement for MS Access
  - kitkat_new 5y ago
    modern in what way?
    - mavhc 5y ago
      Faster, better at handling multiple users, smarter, web based. And also free with whatever package you're already paying for :-)
    - IanSanders 5y ago
      Written in javascript
      - AryanBeezadhur 5y ago
        You mean MongoDB Compass? https://www.mongodb.com/products/compass https://www.mongodb.com/products/compass
      - ComodoHacker 5y ago
        And wrapped in Electron.
    - phoyd 5y ago
      "More Modern" such that it does not contain 8 year old unfixed bugs. https://stackoverflow.com/questions/68034271/how-can-i-get-this-8-year-old-vba-64-bit-compiler-bug-fixed https://stackoverflow.com/questions/68034271/how-can-i-get-t...
      - mushi 5y ago
        You know what database doesn’t contain 8 year old unfixed bugs? A database that’s 7 years old.
- cutler 5y ago
  Filmmaker Pro? Please, no.
otherme123 5y ago
Sadly databases are hard because data is hard. I've seen people trying to stuff one-to-many relationships in a single Excel tab duplicating the one side as many times as needed, e.g. Author_A Book_1 Author_A Book_2 Author_A Book_3 Author_B Book_4 Author_B Book_5 Another strategy I've seen is to stuff the many-side into a single cell, splitted with some less used symbol like |, ; or : . I'm curious to find what would they do when they meet a many-to-many relationship. Horrors surfaced when we analyzed the data: unintended duplications because ids and uniqueness checking was not a thing, shifted columns, undocumented column "formats"... In the end we parsed the whole mess into a DB. I don't see how you could implement a frontend for a database simpler than Access. It was as easy as Excel, with relationships. But reasoning a schema is harder than stuffing all things in a 2D table, and querying a multitable is harder than filtering a few columns.
- zoomastigophore 5y ago
  I agree data is hard. If we could provide a properly designed database which has already defined datatypes but that would be as easy to fill for a standard user as a preformated Excel table that would be great. You're right, Access has this simplicity, I just wish such tools would be available out-of-the-box for other DB engine.
- chux52 5y ago
  The author/book example is actually the preferred method to format data in Excel. From there you can take the entire table of data and put it in a pivot table and answer most questions pretty quickly. For the cells with multiple values, I agree that is a terrible idea.
- beckingz 5y ago
  This format is referred to as third normal form (boyce-codd 3NF). Also referred to as "tidy data" in the hadley wickham world.
Andy_G11 5y ago
MS Access can be directly linked to a spreadsheet for upload / download, or you can copy and paste data directly or import data files. Other databases (MySQL) also allow for import of CSV data.
- cutler 5y ago
  MySQL connector for Excel.
dsr_ 5y ago
What do we know about most database projects? They need CRUD plus queries. What's the easiest way to generate a CRUD form? HyperCard. We need a ubiquitous HyperCard equivalent that talks to the Real Database(TM) of your choice on the backend -- sqlite, postgresql, mysql/mariadb, Oracle, MS-SQL, whatever -- with easy functions for "get a sequence number", "get a unique identifier cookie" and "do this block atomically". I bet there's a few out there right now.
- gopher_space 5y ago
  Filemaker is a neat program.
- CRConrad 5y ago
  Delphi has been around (and better than HyperCard) for twenty-five years. Free Pascal for at least as long, but it has only had the visual component aspect provided by Lazarus for ten or fifteen years.
AryanBeezadhur 5y ago
My preferred database is MongoDB, which comes with a GUI called MongoDB Compass that allows you to visually explore your JSON documents with full CRUD functionality. https://www.mongodb.com/products/compass https://www.mongodb.com/products/compass
ubermonkey 5y ago
There used to be many such tools, or tools that tried to do these things. We lost them.
II2II 5y ago
> I think one of the main problem with databases is how do you populate them. There are several products that will let you populate databases in a table view and easily import CSV files into a database. The problem is with almost everything else. Databases are designed to address a different set of problems. The design of database applications reflects the needs of people who need to maintain a database and make sense in that context. Unfortunately, those design decisions do not make sense in the context of people who use spreadsheets. Doubly unfortunate, a spreadsheet makes a better database than a database makes a better spreadsheet. If anything, the shortcomings of spreadsheets implementation dependent in most cases while the shortcomings of databases are due to the domain they are designed to address. First example: creating a table is more involved than creating a new file and being presented with a blank table. First, a database is created. Then a table is created within that database. Then columns are created with each column, as a minimum, having a type. Only then can the database be populated. Second example: doing a quick calculation is more involved than finding an empty cell and inserting a formula. The only way I can think of doing a calculation within a database involves performing a query. If the calculations are performed within a row, a new column can be created to contain the results. If it makes sense to store a result for each row, but it pulls or consolidates data from other rows, the complexity of the query is going to be interesting. If the calculation involves several rows, there is no obvious place to store the result within the table. There are several ways to deal with that, yet none of them are particularly convenient. If you need to perform a calculation on arbitrarily selected data, well, good luck with performing the query. Oh, and automatic recalculate ... Third example: putting the data into a presentable form is less convenient with databases. It certainly isn't done in the table view since that is just a raw presentation of the data. A form is useful here, but it is a separate step and it isn't going to address all of the functionality found in spreadsheets.
nocodb 5y ago
> I wish there were simple-as-Excel frontends for databases This is exactly what we are attempting to solve at nocodb : https://github.com/nocodb/nocodb https://github.com/nocodb/nocodb Which is, nocodb gives you a google-drive like collaborative spreadsheets on your existing databases (MySQL, Postgres etc) And the original problem in article could be countered with nocodb as we keep audit of all changes done to the database.
- tutuncommon 5y ago
  This looks like a good tool.
rrrrrrrrrrrryan 5y ago
Such tools exist, but aren't popular because they're frustrating. Relational databases are much more rigid than flat files. Columns have datatypes which must be enforced, there are foreign keys, some (combination of) columns may have uniqueness constraints, and so on. All this adds up to a bunch of errors when you're trying to input and manipulate data, because what you've entered is violating some rule someplace in the database. It would be awesome if someone could build an interface that gives users the flexibility of Excel, and communicated the issues with the data they're trying to write in a really intuitive way. (e.g. syntax highlighting for data with issues, mouseovers / tooltips, dropdowns & autocompletes for foreign keys, etc)

imwillofficial 5y ago

I found your point to be incomprehensible.

yodelshady 5y ago

Say "database" and IT says "no, unless we spend a year reviewing requirements". This is only a failure story for every software development route that involves procurement, which is every route except Excel. Those routes tracked, within the only timescale that mattered, precisely zero patients. You want to give smart users DB and DVCS tooling? I'm right behind you. Start asking why they don't have those things already.