News for hackers. Stuff that matters.

A comment on The Tyranny of Spreadsheets. Parent comment

Re:The Tyranny of Spreadsheets by otherme123 on Friday July 23, 2021 @05:57AM (#27928973)
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.

[ Parent | on HN ]
Re:The Tyranny of Spreadsheets by zoomastigophore on Friday July 23, 2021 @07:19AM (#27929444)
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.

[ Parent | on HN ]
Re:The Tyranny of Spreadsheets by chux52 on Friday July 23, 2021 @08:10AM (#27929785)
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.

[ Parent | on HN ]
Re:The Tyranny of Spreadsheets by beckingz on Friday July 23, 2021 @09:34AM (#27930463)
This format is referred to as third normal form (boyce-codd 3NF). Also referred to as "tidy data" in the hadley wickham world.
[ Parent | on HN ]