Resources/solutions/websites for database management with UI component?

November 21, 2016 11:46 AM

As a non-super technical person (at a new company), I was tasked with developing a framework of how to combine a bunch of datasets (currently all in separate .csv files) into one database (preferably a relational database management system) but given that both technical and non-technical people will be using it, the solution needs to have a UI layer that would enable non-techy folks to search/pull/download relevant data from the database on ongoing basis with minimal tech people involvement/supervision. More snowflakes inside.

My background in this does not quite set me up for success - I have minimal coding/SQL background, but I am able to assess needs on a higher level. I am looking for solutions (any and all!) that would enable me to develop this framework (it doesn't have to be fully baked or mocked up). I also have little familiarity with possible vendors out there who can provide these services and/or limited knowledge of forums/list servs where I can ask my questions. So I am reaching out to get some pointers in the right direction where I can start my research either with potential vendors or asking people more knowledgeable than I. Happy to answer any questions here or in direct message - I would be forever obliged.

posted by mooselini to Technology (9 answers total)

You probably don't want to build this yourself. What kind of data are you dealing with and what do you want to do with?

There are all kinds of different database management systems out there that fill all kinds of different needs and there is likely something that will meet your needs. If you provide details here, likely someone can point you in the right direction.
posted by ssg at 11:53 AM on November 21, 2016

While it isn't a tool I enjoy using, I've seen a lot of semi-technical folks use File Maker for doing something just like this.
posted by k5.user at 11:55 AM on November 21, 2016 [5 favorites]

I'd recommend checking out Zoho Creator. It has the ability to import data, create relationships between data, and give people an easy UI to manage data.
posted by beyond_pink at 12:17 PM on November 21, 2016 [2 favorites]

It sounds like you want a CMS (Contact Management System) or a CRM (Customer Relationship Management). This is based on the response you sent me privately, which you could post here.

This is a very common need and something that there are thousands of options for - I'm sure you can find something that will meet your needs. Definitely don't reinvent the wheel for this!

If you feel comfortable posting the fields you need and the use cases here, I'm sure people can help you sort options.
posted by ssg at 12:25 PM on November 21, 2016

Seconding Zoho Creator. I've been using it with a whole variety of non-techy people for a couple of years now. We use shared accounts at various security clearance levels (essentially: admin, editor, viewer) so it's not particularly expensive.
posted by teremala at 1:29 PM on November 21, 2016 [1 favorite]

Seconding ssg; you don't really provide enough information about what you're trying to do to make very good recommendations. For instance, there might be different recommendations if you need to allow end-users to create/update/import data into the database vs. only providing them with a read-only snapshot that they can use for analysis. And there's also the question of what platforms you need to support for the user-facing UI.
posted by Aleyn at 1:44 PM on November 21, 2016

Response by poster: Thanks, all! Ideally end-users would be able to import data that would be cross referenced with already existing entries in the database. Mac OS would be the platform where all the users would be operating on. Back end users ideally would need to develop a set of queries to pull out the most relevant content in the data set.
posted by mooselini at 2:15 PM on November 21, 2016

We just launched metabase at work for this purpose. It will only work after the data is in a rdbms, but it's great. There is a visual query builder, or you can think down to raw SQL. It also gives admins the ability to document the database, it works really week with Google for business credentials. I'm very happy with it.

http://www.metabase.com/
posted by askmehow at 5:20 PM on November 21, 2016

This is actually really hard to do properly, and it's less to do with the technical solution than it is the data and how the data will be used, and then educating people in doing it properly.

Off the top of my head, when evaluating potential solutions, you'd ideally also think about:

(1) What's the scale you're dealing with? Think in terms of both number of csv files and how big each individual file is - are you dealing with 10 csv files of 1MB each, or 10000 10KB files, or 100 100MB files?

(2) How often do you foresee adding new csv files vs updating the data in the files you have (e.g. adding Thing3.csv vs uploading Thing1a.csv) and if uploads should overwrite existing records. And/or if you are going to handle versioning, and at what level.

(3) Relatedly: how it handles reverting an import that was made in error.

(4) Also relatedly: if you want to restrict permissions in some way to ensure no user can accidentally corrupt data in your database, maybe by segregating data uploaded by a user vs data curated by you.

(5) How to handle malformed data (you will be surprised how often even a technical power user messes up a CSV file)

(6) Actually, come to think of it, by upload data do you mean uploading data to add to the db ("here is MyFile.csv which contains a bunch of data on the books I want, please save that and also show me where I can get them", or do you really mean an operation like "MyFile.csv contains 10 book titles I'm interested in, gimme everything you got on them"? Because those are different operations.

(7) How comfortable with data are your end users? Are you dealing with non-technical end users that are secretly Excel power users, or are you dealing with people who just want an easy way to search your csvs? Or do you need to cater to both?
posted by Xany at 10:02 PM on November 22, 2016