For the past few years, I've used Google Spreadsheets when I wanted to create a small database quickly. Database concepts can be implemented using spreadsheet formulas. Each spreadsheet tab represents a database table; Joins and queries use VLOOKUP (or MATCH/INDEX to optimize); GROUP BY queries are either pivot tables or other formulas. As VLOOKUP count increases, performance goes down, but it's worked reasonably well and Google Spreadsheets is free!
However, there are a few issues:
- Foreign key relationships (for example linking an employee and manager, or a student and parent) are fragile. Your choices for joins are limited if your data has no natural key.
- You can manually create a surrogate primary key that will never change for a given row, but:
- You have to type it manually and guarantee uniqueness somehow.
- While you can use data validation to create a pulldown in tab A containing the values of tab B, it requires that the primary key is meaningful enough to recognize the entire row just based on the primary key.
- You can use row numbers to refer to rows in another spreadsheet tab, but that prevents you from sorting or inserting rows in the middle of the sheet.
- You can use direct cross-sheet cell references (like
Children!$C75) which will follow the cell through certain row and column inserts and certain sorts, but does not tolerate deletions well.
For sheets I maintain myself, these issues are small enough I just deal with them. However, last night I found myself looking to set up something like this as a volunteer for a non-profit, where I wouldn't be the one maintaining the data. While researching alternate solutions that at least had a free tier and were also "hosted in the cloud", I found airtable.com.
Similarities to Google Spreadsheets:
- Both let you collaborate interactively on the same sheet
- Both have a revision history (Airtable's is limited to 2 weeks in the free tier, Google's is presumably infinite)
- Both look like a spreadsheet
- Both have APIs for accessing data programatically
Differences from Google Spreadsheets:
- airtable's free tier is limited to 1000 rows. I believe this would be analogous to a google spreadsheet that could only have 1000 rows (across all tabs); Google Spreadsheets limit is 2,000,000 cells (rows × columns).
- airtable has a nice UI for creating links between rows in different spreadsheet tabs with a search field and an autocomplete list that shows you more than one column from the row you're picking. Google Spreadsheets mainly gives you a dropdown list of text using data validation.
- airtable's revision history forces you to take snapshots manually. Google snapshots every revision and tries to group them together intelligently automatically.
Overall, for really small sheets (where Airtable is still free) or in cases where having a user-friendly UI for entering and updating data is paramount and you can afford the cost, Airtable seems pretty nice.