Best database program?

wondering what the concensus might be as far as best database for macos… anyone using “airtable” or
other that u find realtively easy to work with? thanks

Do you want this to run locally on your Mac, or in the cloud? And what are you planning to use it for?

1 Like

I am partial to FileMaker but acknowledge that it is expensive.

5 Likes

Now I’m just sad about Bento again.

24 Likes

Tapforms.


JJW

3 Likes

I grew up on Filemaker and - agreed - while expensive, I love it. I understood it better than MS Access.

3 Likes

Agree with @drjjwmac: Tap Forms.

I’ve found Airtable to be useful for simple databases where sharing is required, or for collecting survey responses from large numbers of people.

For my main use case of collecting confidential patient information for future reference, Airtable or its competitors are obviously not suitable.

I had written off Tap Forms many times because of the name—after all, I wanted a database and not a form application. When I finally downloaded the trial version I was surprised to see that it was exactly what was after. It’s an attractive, actively-developed mac database app built on top of SQLite that is powerful and flexible. It is very reasonably priced, there is an active forum of users, a single developer who is very responsive, great documentation, optional database encryption, JavaScript support and an iOS app with iCloud syncing. My only regret is that I didn’t discover it years earlier. Probably not as good as Airtable or similar if you need a collaborative database but for my use it’s perfect. Would love to hear more about it on the show. Perhaps even interviewing the developer?

3 Likes

I’m a Filemaker developer and my vote goes to Filemaker.

However, depending on what you want to do, how much time you have, etc etc, learning SQL and PHP may be a better choice. The learning curve, however, is extremely different… You can start from zero and build a simple database within an hour with Filemaker, whilst the same thing would take a week with PHP.

/$0.02

Panorama x for heavy lifting with thousands of records, Ninox for everything else especially if you need mobile access

1 Like

mySQL is still free, and it may be the worlds most deployed database system. “Best” is very much dependent on your use case though…

2 Likes

+1 for panX. Its scripting makes it so powerful. You can do almost anything with it!

Postgresql is an amazing and free RDBMS :slight_smile:

1 Like

SQLite is my vote. More portable, can handle almost anything but does require you to learn query language at least a bit. Some of the tools for developing and using SQLite Dbs aid in that.

3 Likes

If you are looking for an easy way to implement a relational database with little to no logic (i.e. no additional logic that goes beyond relational lookups and data filtering, which are a core feature) Ninox might be worth a look. (More complex logic is also possible, but not as easily, see below.)
It’s sort of like a “Filemaker Light”, from my limited understanding of Filemaker.

Basically, you create a relational database with many tables and many fields which can handle 1:1, 1:n and n:m relationships. You can create your own views and forms to properly aggregate information. And you can go beyond this basic functionality and add your own code to different elements.

I really appreciate the fact that there is a native app available for macOS, iOS and iPadOS. With the latest iOS version update, all apps now look modern again. And if it’s only for yourself, just buy the app and use iCloud sync. And if you don’t need collaboration, that’s just a relatively small one-off payment. (I just double-checked and that option still seems to be available, though it’s not promoted as heavily anymore.)

Ninox is still actively developed (though in a rather incremental way it seemed to me from my limited time spent, like “this months, we introduce some additional multiple choice elements to cover one more special use case”) and there is an active community, plus a network of freelancers who will custom-make a solution for you.

Based on my personal research about a year ago, Ninox seemed to offer more functionality than many competitors’ solutions, going beyond “tables but more relational and better to use for collaboration than storing data in Excel tables”. To give you one example (more examples can be found on their website):

I developed (tried to develop…) a database for our company’s machinery (material handling machines, trucks, …) and related repair & maintenance tasks. I could easily add the base table storing machine-specific data including a picture, then add some tables for as many notes as I like, for linking my employees to specific machinery, resources like manuals (which may be shared), etc.
Of course, each machine would be assigned a specific machine type, which is related to a manufacturer and so on.
There was a task table linked to the machine for easy tracking of ongoing tasks, planned maintenance and repair history. So far, so good. Really nice and easy.

And you get “native” iPhone and iPad apps with almost no additional effort along your macOS database.

But, there are some downsides, which Ninox probably shares with most “low code” database solutions which go beyond “smart tables”:

If you want to go beyond storing and representating data using just relations and filters (“Show all unfinished tasks per machine, show all unfinished tasks assigned to employee A, …”), you need to write your own code. That’s not a disadvantage and not too scary, but I found Ninox’ documentation lacking and despite having some hobbyist programming (and database) knowledge in PHP, Python, SQL among others, somehow coding in Ninox never clicked with me. It felt unintuitive to me. This might be entirely my personal problem, because there are many others who don’t seem to share that feeling!
And it seems they have updated their documentation, so maybe they actually listened to their customers’ complaints from one or two years ago. :slight_smile:

If all you need is maybe some simple logic to retrieve additional data (e.g. show me the latest value of operating hours logged for this specific machine in one specific field on the summary page, instead of inserting a small, filtered relational table element which has the same function but doesn’t look as nice), that’s not too hard and you can probably just copy a solution from their forums.

But I wanted to create automatically repeating tasks with different schedules based on different sorts of maintenance plans (in a more complex way than it probably sounds - basically, I wanted to create my own CMMS). That’s entirely possible and I got it halfway done, but I got frustrated by what felt like a trial and error approach I had to take in combination with a generally not-so-good developer experience (code editor without syntax highlighting which block the entire window, using the console to try out code required to first close the code editor, leave the view your working on, …).

There were weekly Zoom meetings, free to attend, where anyone could ask Ninox staff for help or advice with his or her specific problems, though. Probably, if I had tried a bit longer and asked for help there, Ninox would have clicked for me.

Now I’m recreating everything in Python (with Django framework), which has the added benefit of allowing me to write testable code in a dev environment that’s separated from production. But I’ve spent so much more time learning Django than I would have probably needed to get along with Ninox, though the process has been more enjoyable to me personally. :wink:

If your use case can be described as a flexible, relational database, I’ll absolutely recommend you give Ninox a try. It’s so nice to think a bit about the data structure, implement it, polish the interface and after a few hours of work you have a fully functioning “native” iPhone and iPad app alongside your macOS database application. If you want to create a real application with exhaustive logic… it should be possible within certain boundaries but I did not find the process as enjoyable.

Optionally, Ninox can also be used as a web app (plus on Android) if you want to share data. But that’s a subscription service, not a one-off payment.

3 Likes

A different if somewhat related query.

Can anyone recommend good resources for learning SQL?

My needs are to be able to query and occasionally update test databases, in the course of testing web applications.

Thanks!

1 Like

It’s been so long that I can’t recommend a beginner’s guide other than the fact that O’Reilly has always been good in my experience:

That said, if you’re developing in a LAMP type of environment, there’s some slick software called PHPMyAdmin. It’ll handle a lot of the basic SQL stuff, including letting you easily browse tables, search, insert records using a basic form, update data in a spreadsheet view, etc. And for anything it does, it spits out the SQL in the result window so you can actually kind of learn by seeing what it does. It’s bundled by default in hosting control panels (cPanel, etc.)

So before you go all-out to learn the SQL, check and see if you have a tool like that available in your environment. :slight_smile:

3 Likes

It’s been a while but I found the following books helpful:

Beginning SQL Queries

The other ebook I have of hers is now only available as a paper book
Beginning Database Design

2 Likes

I began with Nield, Getting Started with SQL (O’Reilly). Very basic, but I found it useful.

Now I’m working my way through Viescas, SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th ed. (Addison-Wesley Professional). I’ve learned quite a lot from this one. I’m so glad I found it. And it comes with some nice sample databases to use in the exercises.

Before I read either of those, I read Hernandez, Database Design for Mere Mortals (Addison-Wesley Professional). This is thorough but very easy to read. It taught me more about relational databases than anything else I’ve read. I couldn’t have read the two SQL books without the knowledge I got from this one. The author of this one was co-author of the previous three editions of SQL Queries for Mere Mortals.

2 Likes

Also, just as a note - SQL varies slightly in implementation between different SQL servers. This means that if your needs are primarily with a particular flavor of backend, it would be best to come up with a sample data set that you can play with on that particular backend.

3 Likes

+1 for FileMaker despite the need to update every now & then (I started with FMP 10, currently resisting moving up from FMP 16) - but I’m also using AirTable to prototype a friend’s app & it’s great for multi-user scenarios for a lot less than FMP would cost.