Document-Oriented Database for Non-Programmers?
December 3, 2013 1:39 PM Subscribe
Is there such a thing as a Document-Oriented Database for Non-Programmers? I've been searching the web for several days and I'm quite confused so I thought to post here.
These are some criteria for the solution that I imagine in my head. I had hoped to be able to rapidly develop this little three-table thing, but it isn't going quickly for me.
Is such a thing even possible?
- the data is stored as TXT XML JSON or anything that can be portable and opened with any text editor.
- a spreadsheet-like view for sorting/editing items within the data
- forms for adding new data
- Setup and maintain without having to learn javascript, PHP, etc.
- works offline (but using a browser is ok), on PC or Windows
- minimal need for relational-database functions, I think?
In my search, I've heard a lot about NoSQL, HTML5, and so on, but I'm confused. I only really know HTML and CSS.
These are some criteria for the solution that I imagine in my head. I had hoped to be able to rapidly develop this little three-table thing, but it isn't going quickly for me.
Is such a thing even possible?
- the data is stored as TXT XML JSON or anything that can be portable and opened with any text editor.
- a spreadsheet-like view for sorting/editing items within the data
- forms for adding new data
- Setup and maintain without having to learn javascript, PHP, etc.
- works offline (but using a browser is ok), on PC or Windows
- minimal need for relational-database functions, I think?
In my search, I've heard a lot about NoSQL, HTML5, and so on, but I'm confused. I only really know HTML and CSS.
FileMaker Pro if you're on a Mac I think would meet your needs. I'm working with it now for a simple-ish 6-table project. I am also not a programmer, but it supports simple SQL queries which I use a tiny bit.
posted by pantarei70 at 2:01 PM on December 3, 2013
posted by pantarei70 at 2:01 PM on December 3, 2013
A cheap way to do this would be in Excel using VBA for the front end - but I'm not sure how I would go about setting up the "open with a txt editor" requirement. This would be simple do with Filemaker (windows and mac have filemaker) though.
posted by Brent Parker at 2:02 PM on December 3, 2013
posted by Brent Parker at 2:02 PM on December 3, 2013
Does it absolutely have to work offline? I ask because someone figured out how to get JSON data from a Google spreadsheet document.
I haven't tried it myself but you could edit/add data in Google Spreadsheet, publish it and it would available to your prototype.
http://www.nextofwindows.com/how-to-use-google-doc-spreadsheet-serve-json-from-excel-spreadsheet/
posted by cowlick at 2:05 PM on December 3, 2013 [1 favorite]
I haven't tried it myself but you could edit/add data in Google Spreadsheet, publish it and it would available to your prototype.
http://www.nextofwindows.com/how-to-use-google-doc-spreadsheet-serve-json-from-excel-spreadsheet/
posted by cowlick at 2:05 PM on December 3, 2013 [1 favorite]
Response by poster: Access misses the mark for items 1 and 5 on my list. Filemaker misses #1.
Spreadsheets work well, but I'm not sure they accept queries as well as a database would. For example, when filling out one of the sheets, it would be nice to be presented with options available from within one of the others. That type of functionality may only be available via a fullblown relational database, but I'm not sure about that?
It would be nice if this would work offline, if at all possible.
posted by dylan_k at 4:12 PM on December 3, 2013
Spreadsheets work well, but I'm not sure they accept queries as well as a database would. For example, when filling out one of the sheets, it would be nice to be presented with options available from within one of the others. That type of functionality may only be available via a fullblown relational database, but I'm not sure about that?
It would be nice if this would work offline, if at all possible.
posted by dylan_k at 4:12 PM on December 3, 2013
It's hard to answer your question without more specifics about a) what you're storing and b) how you want to query the data. Supporting JSON without having to know anything about javascript is also a confusing pair of requirements. You could use MongoDB (a type of NoSQL database) as a queryable store for some JSON docs, but you need to know at least rudimentary javascript to write Mongo queries.
posted by deathpanels at 4:32 PM on December 3, 2013
posted by deathpanels at 4:32 PM on December 3, 2013
Response by poster: I realize that I may have some confusing/impossible pairs of requirements here, but if I'm going to have to learn something as complex as Javascipt, I want to be sure it really is the path of least resistance before I make that kind of commitment.
a) here's a rough sketch of what I'd like to be storing. This is based on an early attempt with LibreOffice Base, which crashed a bit to often for me to find it practical, so it may assume some things that are native to Base and may not be needed elsewhere. https://github.com/dylan-k/bestrew/blob/master/writers-database_data-model.md
b) You'll see that what I'd like to store is basically three things. They are a little bit connected, in that one of them refers to the other two by name. It might also be useful to do counting queries, for example: how many times has a work, from one table, been involved in a submission, in another table? Or also for example: how many times has a venue, from one table, been involved with a submission, in another table?
I did look at MongoDB and it looks promising. I think that JSON docs or XML files would be nice, if I could only find a suitable GUI for managing them?
posted by dylan_k at 4:47 PM on December 3, 2013
a) here's a rough sketch of what I'd like to be storing. This is based on an early attempt with LibreOffice Base, which crashed a bit to often for me to find it practical, so it may assume some things that are native to Base and may not be needed elsewhere. https://github.com/dylan-k/bestrew/blob/master/writers-database_data-model.md
b) You'll see that what I'd like to store is basically three things. They are a little bit connected, in that one of them refers to the other two by name. It might also be useful to do counting queries, for example: how many times has a work, from one table, been involved in a submission, in another table? Or also for example: how many times has a venue, from one table, been involved with a submission, in another table?
I did look at MongoDB and it looks promising. I think that JSON docs or XML files would be nice, if I could only find a suitable GUI for managing them?
posted by dylan_k at 4:47 PM on December 3, 2013
This appears to be a free version of Serna, an XML editor. That's the kind of thing I think you want - a graphical way to edit standard formatted files. Altova XMLSpy looks seriously nice but it's a few hundred bucks.
NoSQL implementations like MongoDB are really for programming, not a GUI. But if you want to go that way I'd think seriously about using JavaScript and JSON. For whatever reason JSON is like catnip for developers and people are seeing it as a replacement for xml in a lot of places.
posted by graymouser at 5:31 PM on December 3, 2013 [1 favorite]
NoSQL implementations like MongoDB are really for programming, not a GUI. But if you want to go that way I'd think seriously about using JavaScript and JSON. For whatever reason JSON is like catnip for developers and people are seeing it as a replacement for xml in a lot of places.
posted by graymouser at 5:31 PM on December 3, 2013 [1 favorite]
For whatever reason JSON is like catnip for developers...Least common denominator: Because almost anyone can understand all-public key-value pairs - especially if they're only ever strings, and if the hierarchy is flat-ish.
posted by j_curiouser at 6:12 PM on December 3, 2013 [2 favorites]
posted by j_curiouser at 6:12 PM on December 3, 2013 [2 favorites]
Even after clarification, the problem(s) you are trying to solve with this thing you are trying to make isn't very clear, and some of the requirements you list are puzzling.
For example, what is the deal with this?
"the data is stored as TXT XML JSON or anything that can be portable and opened with any text editor."
Is it really important that it be stored in one of those forms, or is it sufficient that you can export the data in one of them? Do you expect to be able to edit the data in a text editor and then interact with it in the database again?
For what it is worth, the thing you mention above that you don't think a spreadsheet can do is, in fact, something Excel can do and I've done it. I can't tell you how to do it though, because I don't have a copy of Excel anymore. As I recall it is related to Data Validation, but different. I can't find anything that looks equivalent in Libre/OpenOffice.
posted by Good Brain at 6:30 PM on December 3, 2013
For example, what is the deal with this?
"the data is stored as TXT XML JSON or anything that can be portable and opened with any text editor."
Is it really important that it be stored in one of those forms, or is it sufficient that you can export the data in one of them? Do you expect to be able to edit the data in a text editor and then interact with it in the database again?
For what it is worth, the thing you mention above that you don't think a spreadsheet can do is, in fact, something Excel can do and I've done it. I can't tell you how to do it though, because I don't have a copy of Excel anymore. As I recall it is related to Data Validation, but different. I can't find anything that looks equivalent in Libre/OpenOffice.
posted by Good Brain at 6:30 PM on December 3, 2013
Response by poster: I apologize if my comments are puzzling while trying to describe things that aren't my area of expertise. Thanks for bearing with me.
Imagine you saved all your data in only a format that excel could read and then Excel ceased to exist, or you needed another incompatible format? At this point in my project, I want my data to be as portable and as flexible as possible. I've already tried two platforms already. Rebuilding takes time. But, yes, exporting would be sufficient.
Excel will give you a dropdown box that presents options from some other sheet, but it won't let you add to that same list via the dropdown -- you can only add to the current cell, which would make things get quickly out-of-sync. I'd prefer, if possible, a databound combo box that can insert new values into both of the relevant places: the cell of the current row as well as a cell for a new row in a related table.
If that kind of thing isn't possible via a flat-file or document-oriented database, then all I need is to know that, but then I need to know if there is a database tool that I can use without an overwhelming learning curve.
posted by dylan_k at 6:50 PM on December 3, 2013
Imagine you saved all your data in only a format that excel could read and then Excel ceased to exist, or you needed another incompatible format? At this point in my project, I want my data to be as portable and as flexible as possible. I've already tried two platforms already. Rebuilding takes time. But, yes, exporting would be sufficient.
Excel will give you a dropdown box that presents options from some other sheet, but it won't let you add to that same list via the dropdown -- you can only add to the current cell, which would make things get quickly out-of-sync. I'd prefer, if possible, a databound combo box that can insert new values into both of the relevant places: the cell of the current row as well as a cell for a new row in a related table.
If that kind of thing isn't possible via a flat-file or document-oriented database, then all I need is to know that, but then I need to know if there is a database tool that I can use without an overwhelming learning curve.
posted by dylan_k at 6:50 PM on December 3, 2013
Wait, what? Access stores most fields as plain text unless you've set it to be a binary. I'm a little confused by what you mean by "opened with any text editor", I guess. Do you want it to actually be stored as .txt files somehow? I mean, yeah, that's generally not something things are going to do, it'd be incredibly inefficient. You can convert Access files to MySQL or whatever, but you're not actually really at risk here of the program ceasing to exist.
I'm not sure how Access requires anything online, unless something's changed significantly since I last used it much in college. You can store stuff on the network and whatever, but you can open it up on your own PC just fine.
What you have there isn't a three-table thing, it's a four-table thing, and it's kind of fundamentally relational, basically, but there are some things that handle relational easier than others. Access does look a bit confusing at first but if the people in my computer applications class in undergrad could manage it, I'm sure you can; you don't have to write raw SQL or anything.
posted by Sequence at 8:00 PM on December 3, 2013
I'm not sure how Access requires anything online, unless something's changed significantly since I last used it much in college. You can store stuff on the network and whatever, but you can open it up on your own PC just fine.
What you have there isn't a three-table thing, it's a four-table thing, and it's kind of fundamentally relational, basically, but there are some things that handle relational easier than others. Access does look a bit confusing at first but if the people in my computer applications class in undergrad could manage it, I'm sure you can; you don't have to write raw SQL or anything.
posted by Sequence at 8:00 PM on December 3, 2013
Response by poster: Thanks but I think I'm going to rule Access out as an option because it doesn't meet my criteria for a platform-independent solution. If at all possible (and I realize it may not be), I'm looking for something I can use on a mac or a pc without having to rely on a web application. The price tag for Access isn't so great either, which is another strike against it.
posted by dylan_k at 8:13 PM on December 3, 2013
posted by dylan_k at 8:13 PM on December 3, 2013
What about Kexi? It's another free Access alternative. It uses binary formats natively but offers CSV export.
posted by scose at 8:20 PM on December 3, 2013
posted by scose at 8:20 PM on December 3, 2013
Response by poster: Kexi looks really great but the heartbreaker for me is the part of their website that says "Kexi is not available for Windows and Mac OS X at the moment." Those are the two computer platforms I'm most interested in, unfortunately.
posted by dylan_k at 8:22 PM on December 3, 2013
posted by dylan_k at 8:22 PM on December 3, 2013
Response by poster: > What you have there isn't a three-table thing,
> it's a four-table thing,
agreed
> and it's kind of fundamentally relational
is it? Ok thanks that helps me to narrow it down a bit.
posted by dylan_k at 8:24 PM on December 3, 2013
> it's a four-table thing,
agreed
> and it's kind of fundamentally relational
is it? Ok thanks that helps me to narrow it down a bit.
posted by dylan_k at 8:24 PM on December 3, 2013
Ragic is tailor-made for this. You edit your data in spreadsheets inside their app, or set up forms to allow end users to enter data online (simple data validation included). All data is portable (csv or excel). For a small fee, you can even add complex functionality via javascript. Not sure about making it work offline, probably possible via javascript I just mentioned.
I do recommend taking an hour to familiarize yourself with their way of doing things before diving into making your "app". They are not completely intuitive right off the bat and may not be suitable for someone who, say, finds linked formulas or Excel macros incomprehensible... otherwise, I think you could have your whole thing set up in a couple of hours.
posted by rada at 10:42 AM on December 4, 2013
I do recommend taking an hour to familiarize yourself with their way of doing things before diving into making your "app". They are not completely intuitive right off the bat and may not be suitable for someone who, say, finds linked formulas or Excel macros incomprehensible... otherwise, I think you could have your whole thing set up in a couple of hours.
posted by rada at 10:42 AM on December 4, 2013
Actually, you can use a xml or csv as a odbc datasource in filemaker and update that through forms (and the spreadsheet view) - so filemaker actually does meet criteria #1.
posted by Brent Parker at 1:35 PM on December 4, 2013 [1 favorite]
posted by Brent Parker at 1:35 PM on December 4, 2013 [1 favorite]
Smartsheet might be worth considering.
It doesn't work offline, and it isn't editable with a text editor, but it might suit you best from a data structure/data entry/usability/features point of view.
posted by Sharcho at 2:14 PM on December 4, 2013 [1 favorite]
It doesn't work offline, and it isn't editable with a text editor, but it might suit you best from a data structure/data entry/usability/features point of view.
posted by Sharcho at 2:14 PM on December 4, 2013 [1 favorite]
Response by poster: The Open Knowledge Foundation has published a standard for a "data package" that looks promising. Essentially, it is a combination of JSON and CSV files. It can be edited in a serverless way. It can be easily transferred from one type of data system to another. An added bonus to all this is that the data can then be managed inside of a Git repository.
posted by dylan_k at 3:02 PM on January 27, 2014 [1 favorite]
posted by dylan_k at 3:02 PM on January 27, 2014 [1 favorite]
This thread is closed to new comments.
posted by Sequence at 1:49 PM on December 3, 2013