I always see the programmer sentiment that “people should not be using excel for (project). It should be a database.”

I am guessing by that they mean SQL? I dont know a thing about that except the acronym. The most ive done is a basic linux script and 2 hours of a VBA course. I suck at understanding programming, so id like to understand exactly how one uses a database vs excel. Like whay are the actual steps id have to do?

  • Unlearned9545@lemmy.world
    link
    fedilink
    arrow-up
    6
    ·
    8 hours ago

    Most databases don’t require a license, nearly infinitely scalable, robust, can handle mass edits and multi edits and can be interacted with by humans and variety of programs easily. SQL is a language commonly used to interact with relational databases.

    Excel is limited to how much data can be stored in it, it has auto format features that will change what you type into something different (like auto interpreting things that look like dates) and each cell can either be data or equation. It can also be tricky to add lots of data at once, licenses are not free and multi people editing it at the same time can be tricky.

    The limit on how much data can be stored in a db is typically orders of magnitude higher, you typically specify beforehand what type of thing is in each column (string, date, number, etc) cells can only hold data not equations they are built for adding lots of data en masse safely and can handle lots of “simultaneous” editors.

    Geneitc scientistist have had to change they way they name genes to avoid auto formatting into dates in Excel. UK government attempted to track early COVID cases with Excel but the data was getting capped by the max size of Excel. NZ gov lost track of lots of money in their budget because of lack of being able to multi edit.

    A relational database is a bit like a rigid excel workbook. You have multiple tables that are made if rows and columns. A column is an attribute (like population or height) and a row is a data entry (like town). Some of these columns can store keys to rows in other tables to be used as a reference. In our example case a table of townships might link to another table of counties or states or countries.

    A non relational database is more like a collection of personal notes about things to allow for more flexibility.

    I highly recommend starting with Grist or TablePlus both of which have nice GUI, free tier and can get your head around databases.