Simply, what you want to do? If you want to get more out of a CMMS or ERP systems that is hosting your maintenance data, then you better learn about dbase. Otherwise, if you want to create a system to manage your small to medium business or store, the you don’t need to learn a dbase. So, what I can do if I want to manage around 10,000 store items or tasks? That’s what we are going to speak about in this article.
Spreadsheets vs dbase
To start with, Spreadsheets are human centric or user centric. While, dbase are built to be accessed by apps. Usually you want to structure, add, delete, retrieve or analyze data in a dbase. in a dbase. You can do this using Queries which are SQL commands from the command line or though an interface app.
Spreadsheets are simply any app that can store your data in the form of rows and columns. Moreover, it allows some analysis for this data through a GUI (Graphical User Interface). We described what you are going to store as data because it is now a set of structured and accumulated information. There are legendary spreadsheets that pops up to your mind when you hear the word “spreadsheets”. The most famous regardless you are using it or not is the Microsoft Excel. The second one is Google Sheets. Google Sheets keeps on growing and adding features with years. Once you have a gmail, it is free for you to use and it can store data in up to 5 million cells in one spread sheet. Even more, now you can build an app on top of your sheet with a simple drag and drop web app called AppSheet.
On the other hand, a dbase is a term which we use in short for a database system. You can refer to more details about dbase in our article: Digital Skills for Maintenance – Part1: dbase. However simply, it is a set of structured information with software to manage it and you need SQL –Structured Query Language- · SQL lets you access and manipulate databases. The data tables that include the data ae stored in sheets in the spreadsheet app. Same structures are called tables in dbase rather than sheet. Moreover, rows in sheets are records in dbase and, columns in sheets are fields in dbase.
Regardless of any pros and cons of using a dbase over a spreadsheet, if you are obliged to master dbase operations as a job requirement, spreadsheets won’t solve your problem. On the other hand, if you are deliberately starting to build your own system, Spread sheets will make your day. Moreover you can migrate your Sheets to dbase later on.
What is Data Structuring?
There are 2 main activities in handling data. First one is storing data and the second one is managing data. Storing data is just keeping the information safe inside a file so you can return to it in the future or add to it regularly. Managing data include all the activities you do on this data. So you manage your data when you modify, update, control, and organize it. However any of these activities needs a vital preliminary step; data structuring.
Structuring data is simply organizing it so you can easily, quickly and efficiently retrieve data. Moreover, you can look into data patterns easily using any mathematical function, trend or charts. However when you are working with Sheets or any other spreadsheets app, you can easily reorganize your data tables. And you can do this visually till you are satisfied with the data organization and the results. Even more in some apps like Sheets you have some sort of AI. It gives you some recommendations about your data. On the other hand, in case of using a dbase, it is harder to restructure your data. You might need to create a new table to add columns for an extra information you want to log.
It is not far from organizing your local store or your tools. Having the parts or tools is not enough that you can use them. You need to keep them in a good condition and easily and quickly to reach. This requires a clearly structured and labelled storage space. Otherwise you might not find a part or an accessory for a tool although you are sure that it exists.
What can I do using Sheets?
When you have sometime review our article on how easier solutions are the most sustainable ones: Digital Skills for Maintenance-Part 2:Make it easy. That’s where Google in its web apps as Sheets excels.
First, you need to know the information you want to store and manipulate. Then make a simple visualization or demo on how you want to store them. It is better with an example. Let’s think about a local spare parts store. As usual with any data storage, you store your data in a table i.e. in rows (records) and columns (fields). So, the demo will be for few items from one category. Then, you need to think whether you need a serial number or not. For items in the store usually they have item numbers, so no need for serialization specially if you insert and delete frequently. After that, what data you need to add beside each item in the store? You can start by Item code, storage location, quantity, description, min/max stock, etc. You might have some other attributes like who use it? Who purchase it? Transaction dates, etc.
You might be in doubt of how much A spreadsheet from Sheets can carry data? Really for for a complete production site with many production facilities, all its spares can fit in one spread sheet easily if if they reach to 100,000 items. The math is simple. Sheets offer 5,000,000 cells per spread sheet. So if you will use 100,000 rows, you are left with 50 columns of data and transactions per item. When the number of items decrease you can increase number of columns so in case you have only 10,000 items then you can dedicate 500 columns to each item.
Anyhow, the number of columns is limited to 18,278 column. So, if you need them all, you will have only around 273 rows available under each column. There is another limitation which is the number of sheets per spread sheet. Maximum you can divide your data into 200 sheets or tables as in dbase notation. As a personal recommendation, I like to leave and shade one or two rows at the top. Those I use them for remarks, calculations, summaries or even buttons for automated tasks. Similarly from the left, I leave and shade a couple of columns.
Does dbase have some limitation as those of spreadsheets?
There no system that has no limitations. The dbase tables looks huge for a single user but they are limited to 2 Gbyte per file so the operating system can handle them. Also, dbase lives and runs on servers. So, they are limited by the capacity of the server to serve requests. Moreover, the complexity of the dbase structure may add delays to accessing the requested data out of the dbase. Another example is storing images. Sheets from Google can simply add images in the sheets or even inside cells and resize them based on user preferences. In dbase, you need a 3rd party software to modify the image into data. The, it is stored as data. To retrieve it back, you need a software to assemble the image out of the data which is time consuming process.
Now how to manipulate data and can we automate some tasks about it? This will be in another article.
In conclusion,
You have a clear overview now whether you need to use dbase and DBMS -Data Base Management System- for your next project. Or do you need simply a spreadsheet app. In case that it is mandatory to use a dbase to be able to integrate it smoothly with some existing system, then go ahead to master the dbase and SQL. Otherwise, Sheets is a great option for easier and quicker stunning results. Follow up with us along the coming articles.
If you feel you need help with any of these ideas we discussed, drop us a line for initial investigation in the form by Clicking here or request Management Consultancy or Coaching Services From our Store
