Unanswered: Using MySQL or perhaps another Database for my small business needs
I currently have a "database" that consists of 7 separate tables in Microsoft Access 2010, several of which are around the 2GB maximum size for tables in Access.
I am trying to get some recommendations on how best to go about transitioning to a more robust Database, particularly which platform would be best for me to use, whether it is MySQL, or SQL Server Express, or some other option. One recommendation someone suggested is to simply create a MySQL database that I can then access from Microsoft Access as the front end interface, using an ODBC connection. Does that seem like a good course of action for me to take. See basic business use of the data below.
I will primarily be using the Database to organize (i.e, read and write to the tables), analyze, and select product inventory, and to build some date-based transaction reporting. So, the types of queries I am currently doing are relatively simple and straightforward 'Select' queries, with additional 'Where' statements to filter down the list of products. Our company's employees are currently limited to basic SQL skills, and strong familiarity with Microsoft Access and Excel, but minimal programming skills in both. We both don't have any database admin skills, so we plan to hire someone using guru.com or upwork.com. So we are looking for a simple front end solution, keeping in mind that we are often updating the data files, deleting the data, appending to it, etc. We currently are doing so in Access and Excel using txt and csv files.
There are about 5 million products total, and about 60 columns of data in the Main Inventory table that is probably in the most need of being placed in the new, larger, database since it is already about 2GB in size within the Access Table (i.e., I can't make it any larger in Access).
It probably isn't worth mentioning at this point but I would like to eventually (but not immediately) further transition the Database to much more advanced analysis capabilities that may require MapReduce functionality, or Clustering of data for efficient processing. This will likely be placed in the Cloud via Azure or Amazon AWS. So, ideally, whichever tool I use could also be a good stepping stone to Big Data Analytic capabilities. However, I realize at that point, it may be a complete redesign, so feel free to ignore this comment, if it doesn't make sense to even consider it for the initial, current need I have which is to simply provide basic query access to larger data files than I can currently access within Access.
Thanks for any thoughts or recommendations or general guidance you may have for me, as I try to take the right steps for my small business to manage a large amount of product data.