Businesses today run on data. Whether it’s information about your customers, transactions, or employees, you need to make sure that you safely and effectively store data in 2020.
It’s astounding how many companies still do not use some kind of database solution. Here are some of the approaches that we’ve seen businesses take that can cause problems:
More often than not, we see a mix of paper files, excel spreadsheets, and digital files. Some businesses are able to have a framework around it that is somewhat organized, but often things are just on the verge of getting out of hand. Thing can rapidly get more complex when dealing with a mix of data types like this, and when you’re trying to scale your business quickly, things can get unsustainable fast!
So what database solutions are available for business in this kind of situation? Let’s break down a few of the most common options and address some things you may or may not have heard about them.
FileMaker and Access are database solutions from Apple and Microsoft respectively that are meant for small companies with relatively simple data needs. The big sell with FileMaker and Access is their ease of use – anyone is supposed to be able to learn these database products, and you can easily organize your business data in one place with them. They also offer a suite of integrations with other products and services, which can be very useful.
While the products have similar feature sets, FileMaker is typically a more pleasant experience to use, and it has the advantage of working on both Mac and Windows. Access is a little rougher experience, and only works on Windows, but there are a lot more Access pros out there.
If you’re a business just getting started in the database realm, FileMaker or Access is a good place to start. You want to make sure you have someone who understands the business and is data savvy to be the gatekeeper of the database, and you keep just one copy of the database on a computer or server at the office (and make backups of that). With these basic controls in place, FileMaker or Access can store hundreds of thousands of records and can take you along way.
Once your business gets the point that you’re storing hundreds of thousands to millions of data points, you may want to consider using a traditional relational SQL database for your business needs. Particularly, if you want to start creating custom integrations with other systems that leverage your data, the possibilities are endless with a SQL database.
SQL stands for “Structured Query Language”, and that is the programming language used to write queries that retrieve data from a SQL database. While it’s not difficult to learn the basics of SQL, it’s important if you are going to deploy a SQL database to have an employee or consultant who is fluent in SQL and can help you to set up and maintain your data within the SQL database.
Overall, the process of using a relational SQL database is much more complex than a product like Access or FileMaker, and requires not only specialized knowledge to maintain and integrate, but also a more robust server to host the data. There are far fewer options for direct “user access” within most relational SQL databases as well, due to the complexity of the database environment. This means that the average office user cannot just get in there and create ad-hoc reports. Instead, you must rely on additional reporting software for this, such as Power BI or Tableau. This isn’t necessarily a bad thing though – as it can provide you with an element of control that isn’t present with some other database solutions.
Despite the complexities, a relational SQL database offers a high level of scalability, and nearly infinite integration options. If your business is growing rapidly and is heavily data dependent, a relational SQL database may be the best option for you. There are many options available for relational SQL databases as well – from the free MySQL platform, to the enterprise grade Microsoft SQL Server. One of the things that’s nice about these database platforms is that there are a plethora of people out there who know how to work with and maintain them, so getting either an employee or a consultant to help you isn’t too difficult (although it can be expensive).
You might have heard someone telling you that a NoSQL database is the fastest type of database you can get right now, which is an intriguing statement. While the idea of a NoSQL database used to just be a buzzword, they have matured in the past decade into a legitimate contender for business data in some use cases.
NoSQL databases rely on storing files in very fast memory with complete records of information. The great thing about this is, when you query the database to retrieve data, you get a complete record set, and you don’t have to join in additional data pieces in like with a relational database. This does indeed make them very fast, but you must be careful about how you store data in this format to make sure you don’t store too much unrelated data in a single record.
Most businesses will do just fine with a traditional relational SQL database. In fact, most enterprises still use them. However, if your business stores particularly complex “point in time snapshots” of customer data that need to be rapidly retrieved and modified at scale, you might be a good candidate for a NoSQL database. This is something that you should discuss with a database expert before making a commitment to though. Don’t be lured in by the promise of lightening fast data access – your business needs to have a very good reason to choose this kind of database!
There are several different NoSQL databases available, but the most popular currently is MongoDB. It’s relatively easy to get started with MongoDB if you have the right use, and there are MongoDB professionals out there who can help you get started if it’s right for your business.
A decision that goes hand in hand with what database to use is where to host the database. You can either choose to host your database in a machine/server on-premise, or use one of the many available cloud providers to host your database.
All three of the above solutions can be hosted both on-premise and in the cloud. Here are our suggestions for each:
Access and FileMaker databases both live in a single file or small set of files. Because of this, it’s easy to move around an Access or FileMaker database. With this portability in mind, and especially if you’re using Access or FileMaker as your first database, we suggest storing the database files on-premise. If you’ve already got a simple network share set up, you’re good to go. Just make sure that you restrict permissions to the spot where the database is stored to just the people who need to get to it.
If you don’t have any kind of network share, the database can live on the computer of the person who’s primarily responsible for updating it. While this can make shared access more difficult, often in this situation that allows one person to be a gatekeeper of the data, which can be very beneficial.
There are options for hosting FileMaker/Access databases in the cloud as well. If you have a larger organization and need to streamline shared access or need to facilitate shared access but don’t have any kind of network share set up, this may be the way to go.
One last consideration for this type of database is backups. You’ll want to ensure whether you are hosting it on-premise or in the cloud that you make regular backups. If you’re on premise, this can be as simple as copying your database file to another drive on a regular basis. If you’re in the cloud, often-times the cloud provider has options to handle this for you.
In order to host a relational database on premise, you have to set up a dedicated database server. Depending on which variety of relational database you want to use, this can range from fairly simple, to very complex. It’s important when using a relational database to think about redundancy and backups as well, as there will most likely be a lot of business critical data on this server. This can be particularly complicated to set up if you don’t have a DBA (Database Administrator) with the expertise to do so.
There are many options for hosting relational databases in the cloud that can make the process significantly easier, making it our typical recommendation. Platforms like Microsoft Azure and Amazon Web Services allow you to use their PaaS (Platform as a Service) offerings to spin up relational databases with the click of a button. The other great thing about hosting in the cloud is, short term backups are handled for you, and it’s usually much easier to create a secondary database in a geographically diverse location, ensuring you’ll always have access to the data.
While cloud databases can start out very inexpensively (~$5/month), a more robust/performant cloud database can get expensive. If you’re business is dealing with millions of records, you may need to pay several hundred dollars per month for your cloud database. If you just happen to already have on-premise servers that are up to the task, that cost difference may be a consideration to think about.
Hosting a NoSQL database on premise has many of the same complexities as hosting a relational database on-premise, so we won’t rehash that here. Just like with a relational database, there are many cloud offerings for NoSQL databases. Since it’s expected that if you’re using a NoSQL database, high transactional volume and large amounts of data are at play, cloud NoSQL database offering are particularly good about helping you to set up your database in a geographically redundant manner, so that’s typically what we recommend.
The decision where to host your NoSQL database may also come down to what personnel you have available, just like with a relational database. If you’ve committed to going the NoSQL route and end up with a NoSQL database administrator in house, they may recommend on premise if you already have the server infrastructure available.
Modern business run on data, so it’s important to take some time to think about where and how it should be housed. Whether you’re starting a new business and have the luxury of choosing a new database platform without any baggage to consider, or you’re attempting to refresh your company’s database setup, it’s critical to consider what database platform is the best fit for you, and where it should live.