Best simple database
June 16, 2019 10:02 AM Subscribe
Looking for a very low cost database to replace a massive spreadsheet of names and contact information.
I have a spreadsheet with about 3000 names on it. I'd like to convert this to some kind of database tool. We need to track some details about each individual (name, email, membership in certain sub groups) as well as participation in various ongoing activities. For example. If 10 people who are already in the database attend a specific training, I'd like to be able to note that in the database so later we can go back and track that over time, or find all the people who have ever participated in that training. Ideally we could keep adding new things to track, as we create new trainings and activities.
We'd like to link this database with an email service (likely mailchimp?) to be able to send messages to various groups, such as everyone on the list, or everyone who attended a specific training.
Generally, I prefer to pay for software rather than use free options but this is something I won't be reimbursed for so free or low cost is better for me in this case.
I have a spreadsheet with about 3000 names on it. I'd like to convert this to some kind of database tool. We need to track some details about each individual (name, email, membership in certain sub groups) as well as participation in various ongoing activities. For example. If 10 people who are already in the database attend a specific training, I'd like to be able to note that in the database so later we can go back and track that over time, or find all the people who have ever participated in that training. Ideally we could keep adding new things to track, as we create new trainings and activities.
We'd like to link this database with an email service (likely mailchimp?) to be able to send messages to various groups, such as everyone on the list, or everyone who attended a specific training.
Generally, I prefer to pay for software rather than use free options but this is something I won't be reimbursed for so free or low cost is better for me in this case.
You can do this with Airtable without needing to know about how to code a front-end or maintain a website or figure out what the hell LAMP is or anything else you'd need to learn to use MySQL. It's not free but maybe the cost will be OK.
posted by bleep at 10:21 AM on June 16, 2019 [2 favorites]
posted by bleep at 10:21 AM on June 16, 2019 [2 favorites]
I'd suggest sqlite. Free and libre, with no install necessary. Just download the binary for your OS and run it. It can be used as a command line tool but there are also GUI's for it such as SQLiteBrowser.
posted by Poldo at 11:05 AM on June 16, 2019 [3 favorites]
posted by Poldo at 11:05 AM on June 16, 2019 [3 favorites]
I think what you need it's a CRM. Don't have a specific recommendation though.
posted by pyro979 at 11:06 AM on June 16, 2019 [1 favorite]
posted by pyro979 at 11:06 AM on June 16, 2019 [1 favorite]
Mod note: This is an answer from an anonymous commenter.
PostgreSQL. Free, open source, well supported with a huge community on IRC, slack, and email lists. The postgres documentation is pretty good and being updated currently. You can also make tables for the events with location information and organizer information. You can put the postgres instance in a virtual machine.posted by cortex (staff) at 11:52 AM on June 16, 2019
If you need a relational database (which seems like the right family of database for your needs), MySQL, MariaDB, PostreSQL and SQLite are all probably decent choices. SQLite stores data in a single flat file instead of running an actual database process, so may be easier for you, but it also consequently technically isn't a database. (Two people writing to the file at the same time might give you a bad time.) You will probably want some sort of tool to give you a GUI for running queries (though you can also use a command prompt), like DBeaver Microsoft Access isn't something I would ever turn to because I'm quite unfamiliar with its specifics and it costs money, but a long time ago it was seen as a reasonable small business solution that could be obtained cheaply for not-for-profits via TechSoup. If that fits your particular situation, it might be good.
That said, a database is not necessarily the most user friendly thing in the world, and it's going to have to live somewhere and be accessible from somewhere. I can't speak to your particular constraints, but making a DB usable across your local network will take some configuration. If it's just you, all of the DBs above are fine. If handling network config is an issue - well, you can pop a SQLite file in a shared folder, and for the rest you'd want to check the documentation for setting up network access. You might also want to look up hosted solutions - AWS and Google Cloud can be quite cheap for small things, though both might be overkill for a database of this size.
Speaking of size: your spreadsheet has 3000 rows, it may be too large to navigate easily, but ostensibly Excel and Google Sheets are comfortable with approximately a million rows at the moment. If you like your current interface and are comfortable with pivot tables & all that jazz to generate appropriate subsets of contacts, sticking with the spreadsheet might be unpleasant but adequate. Alternately, use something like pandas to ingest the spreadsheet, run your query, and then spit out the results.
As noted by Bleep and pyro979, a database might indeed not be quite the thing here - there's AirTable, and various open source CRM solutions that might also be better suited to this specific problem that will give you a nicer front-end for your data.
posted by Going To Maine at 12:15 PM on June 16, 2019 [1 favorite]
That said, a database is not necessarily the most user friendly thing in the world, and it's going to have to live somewhere and be accessible from somewhere. I can't speak to your particular constraints, but making a DB usable across your local network will take some configuration. If it's just you, all of the DBs above are fine. If handling network config is an issue - well, you can pop a SQLite file in a shared folder, and for the rest you'd want to check the documentation for setting up network access. You might also want to look up hosted solutions - AWS and Google Cloud can be quite cheap for small things, though both might be overkill for a database of this size.
Speaking of size: your spreadsheet has 3000 rows, it may be too large to navigate easily, but ostensibly Excel and Google Sheets are comfortable with approximately a million rows at the moment. If you like your current interface and are comfortable with pivot tables & all that jazz to generate appropriate subsets of contacts, sticking with the spreadsheet might be unpleasant but adequate. Alternately, use something like pandas to ingest the spreadsheet, run your query, and then spit out the results.
As noted by Bleep and pyro979, a database might indeed not be quite the thing here - there's AirTable, and various open source CRM solutions that might also be better suited to this specific problem that will give you a nicer front-end for your data.
posted by Going To Maine at 12:15 PM on June 16, 2019 [1 favorite]
We use Airtable for exactly this at my office. We all love it and we've converted several other departments to also using it for various things.
posted by soren_lorensen at 12:17 PM on June 16, 2019
posted by soren_lorensen at 12:17 PM on June 16, 2019
A database is just the bare bones for storing and querying your data. It would probably be the base infrastructure for any system that does what you want, but just setting a database up and putting your data in there won't get you where you want to be.
Looking at a CRM is a good idea, and there's good low cost or free options around but to get a good answer it would be useful to know what your technical skills level and resources are.
posted by each day we work at 12:18 PM on June 16, 2019
Looking at a CRM is a good idea, and there's good low cost or free options around but to get a good answer it would be useful to know what your technical skills level and resources are.
posted by each day we work at 12:18 PM on June 16, 2019
If you are specifically interested in tracking training and related activities, you might look at a Learning Management System (LMS). Moodle is a well known one that might be worth looking at.
If you are already using Excel for a spreadsheet, look at Access for a database. Its not as flexible or powerful as most of the other options listed here, but its likely already part of your MS Office package and will have a smaller learning curve.
If you decide to build your own DB using any of these options, spend a little time looking at how Relational Databases work. A basic understanding of how to set up the data will save you a lot of frustration later as you add on new things you want to track.
posted by nalyd at 1:10 PM on June 16, 2019
If you are already using Excel for a spreadsheet, look at Access for a database. Its not as flexible or powerful as most of the other options listed here, but its likely already part of your MS Office package and will have a smaller learning curve.
If you decide to build your own DB using any of these options, spend a little time looking at how Relational Databases work. A basic understanding of how to set up the data will save you a lot of frustration later as you add on new things you want to track.
posted by nalyd at 1:10 PM on June 16, 2019
Probably you need two separate things. You need a database to hold your data, and you need an interface program via which you can add data to the database and extract it report form. You can get these two things in one product, e.g. Access, but some of the products mentioned above don't have a very complete interface or report function.
You extract data from database products using a specialized computer language, usually some sort of SQL. There are programs that allow you to create SQL via point and click which are easier for beginners to learn than writing the code, so you want choose a product with that capability. A somewhat rudimentary example is Excel\Data\From Other Sources\From Microsoft Query.
I agree with nalyd that reading up a bit on relational databases would be time well spent. Choices you make in the beginning may be with you for a long time.
posted by SemiSalt at 2:32 PM on June 16, 2019 [1 favorite]
You extract data from database products using a specialized computer language, usually some sort of SQL. There are programs that allow you to create SQL via point and click which are easier for beginners to learn than writing the code, so you want choose a product with that capability. A somewhat rudimentary example is Excel\Data\From Other Sources\From Microsoft Query.
I agree with nalyd that reading up a bit on relational databases would be time well spent. Choices you make in the beginning may be with you for a long time.
posted by SemiSalt at 2:32 PM on June 16, 2019 [1 favorite]
As a counterpoint, I had a dataset that was outgrowing Excel and on the recs of some friends I looked into AIrtable.
It's really just an online spreadsheet, near as I can tell. It probably answers data-sharing needs better than a desktop spreadsheet, but it is absolutely not the answer for someone who needs to transition to a proper database with multivariate queries, etc.
posted by uberchet at 7:00 AM on June 17, 2019
It's really just an online spreadsheet, near as I can tell. It probably answers data-sharing needs better than a desktop spreadsheet, but it is absolutely not the answer for someone who needs to transition to a proper database with multivariate queries, etc.
posted by uberchet at 7:00 AM on June 17, 2019
If you are interested in a CRM (it is what I would use in your case), I'd recommend HubSpot (I'm just a user). You don't need it for anything crazy, so it would be free, for as many users as you wish. You can add custom fields as you see fit, and add notes and tasks to contacts, etc.
There is also a pretty good learning centre and community if you need any help.
[Edited to add...] It's also quite easy to use, and you can upload your contacts from Excel. Just create any missing fields from your list in the CRM first and map your columns to the new fields.
posted by Laura in Canada at 8:16 AM on June 17, 2019
There is also a pretty good learning centre and community if you need any help.
[Edited to add...] It's also quite easy to use, and you can upload your contacts from Excel. Just create any missing fields from your list in the CRM first and map your columns to the new fields.
posted by Laura in Canada at 8:16 AM on June 17, 2019
Airtable was designed for the situation described here -- you have an excel spreadsheet, you want to do some simple database-like tasks on it, and don't want to administer a database or write any code. You can use their concept of "Views" to group people together or to do a search limited to specific properties (like participation in certain groups).
Unfortunately with 3000 individual entries in the database, you are above the threshold for their free accounts. It would be $10/user-who-needs-to-see-the-database/month.
You can use Zapier to connect to Mailchimp.
Of course all of this could be done with a bunch of scripts and a real database (for only a few thousand rows SQLite probably is fine) and maybe a custom web app. This could be an option worth looking into if you have access to volunteer programmer labor.
posted by vogon_poet at 9:14 AM on June 17, 2019
Unfortunately with 3000 individual entries in the database, you are above the threshold for their free accounts. It would be $10/user-who-needs-to-see-the-database/month.
You can use Zapier to connect to Mailchimp.
Of course all of this could be done with a bunch of scripts and a real database (for only a few thousand rows SQLite probably is fine) and maybe a custom web app. This could be an option worth looking into if you have access to volunteer programmer labor.
posted by vogon_poet at 9:14 AM on June 17, 2019
« Older Entry-Level Freelance Writing Gigs? | Transport a cat internationally in hot weather Newer »
This thread is closed to new comments.
posted by ubiquity at 10:16 AM on June 16, 2019