Database or spreadsheet?
Sometimes we use spreadsheet to store data as table just like database. Especially the features provided like sorting and filtering. This allows us to look at the data easily without the knowledge of SQL (structured query language). Furthermore, we can add in the rows easily, do calculation using formulae, and also copy-paste the data to any cell. Comparing to database, the data needs to be filled row by row, this is the main drawback. This is why spreadsheet is easier to use.
We can also use spreadsheet to detect duplicated value using sorting, followed by a new column to check the value with the previous row, eg “=if(A2=A1)”, then we can remove all the duplicated row. Besides that, there are other functions that can be used such as concatenate().
However, there are some cases which must be done in database. For example, if there are two sheets of data, namely sheet A and sheet B. The sheet A contains some user information such as (user ID, username, email address); sheet B contains (user ID, gender, contact no.). If both sheet A and sheet B has the same number of rows and matched user ID, then both sheets can be combined easily using spreadsheet by using sorting. However, if the number of rows is different, or the user ID are not matched, what is the solution?
The above problem can be understood using the set theory, where the sheet A and sheet B are the set A and set B. If the set operation is converted into the programming, it will be exhaustive because we need to compare the user ID for set A and set B programmatically. The easier and better solution is using database SQL. Based on the output we need, we can use JOIN clause as the set operations. This is the wonderfulness of database SQL.
There is another problem, that is converting the spreadsheet to database and vice versa. Converting the LibreOffice Calc format to LibreOffice Base format can work. But I didn’t try this before. Therefore, my best solution is to use the MySQL, using phpMyAdmin interface. This is because phpMyAdmin allows us to import the OpenDocument Spreadsheet (ods) format, and even export to OpenDocument Spreadsheet format. Yes, this is how I solved the spreadsheet problems and perform the set operations using JOIN clauses in MySQL.