I am looking for database advice. I am generally comfortable with computers but a complete novice with databases.
The setup:
In case it matters, I used a Mac, not Windows, while my main collaborator on this is somewhat platform agnostic. I think I can get our IT people to run a server on a shared machine, so that we can both access the data. I've seen websites (Macintouch, mainly) recommend different database software:
What advice do you have?
The setup:
- We have a collection of data currently maintained as several spreadsheets, and data entry often requires entering the same information into several different spreadsheets. This is stupid and annoying and leads to mistakes. We also want to extract data in ways that it not currently easy to do.
- Two of us control the data. My colleague does at least 75% of the data entry and I do the rest. The two of us each use the data, probably in different ways. No one else has access, but I could imagine extracting pieces of the data for wider distribution, if it was easy to do.
- The data has this sort of flavor: we have people, products, and dates. One spreadsheet (for a given year, say) will have a row for each person (or maybe several rows for each person), a column for each month, and each entry will show a product connected to that person in that month. Another spreadsheet might correspond to a single month and have rows for each product, but with extra data (not present in the first spreadsheet) attached to the entries. We might also want to look at a given person and see what they've done over the past 5 years, or we might want to look at a given product and see who's been involved with it.
- So it seems like converting the information to a database is the obvious choice. (Right?) Then we want ways of extracting data to produce the two current spreadsheets, plus get other information, and I'm hoping that's easy to do with standard database software.
- I am comfortable extracting data from the current spreadsheets (dump to a csv file, write some Python code to manipulate it, then write a new csv file). And it's sort of a mess currently, so I'll probably have to do that before importing it into a new system.
In case it matters, I used a Mac, not Windows, while my main collaborator on this is somewhat platform agnostic. I think I can get our IT people to run a server on a shared machine, so that we can both access the data. I've seen websites (Macintouch, mainly) recommend different database software:
- PanoramaX: https://www.provue.com (OS X only?)
- Querious: https://www.araelium.com/querious (OS X only?)
- Valentina: https://www.valentina-db.com (multiple platform)
What advice do you have?