Looking for an appropriate DBMS

Question

I have some applications I want to write that have similar data needs and I'm having trouble figuring out which DBMS would be best. I want something open source, with a reasonable license (not MySQL).

One application is a "family information" application which is a combination contact, family tree, family history, and photo/metadata tracker. The data needs are thus fairly moderate (by modern standards).

Part of the problem is that I'm not entirely sure that what I want to do would be easy, or even possible in an RDBMS, which is pretty much all I'm familiar with. I might even need to use multiple DBMS's for different aspects.

The data I want to store contains three main parts: Parent nodes, data nodes, and properties.

A parent node consists of identifying information, a list of child nodes, and a set of properties.

A data (child) node contains some kind of content, which will either be text or binary data, and a set of properties.

Properties are key/value pairs, where the value is something reasonably simple - a primitive (boolean, byte, int, double, etc), date, enumerated type, or a reasonably short string. The properties are the part of this that I'm having the most trouble with - figuring out how to store properties of different types in a way where they can be transparently accessed despite the different data types.

Please recommend some kind of data storage library that would allow me to implement such a system with a minimum of difficulty in a Java Virtual Machine environment (Java and/or Scala).

Answer 1

A free database I've used and liked is Firebird. It can be used locally or as a small-to-medium multiuser DB, has an extremely small footprint, and there are a number of libraries available for it. It is low weight and low maintenance, and conforms to ISO/IEC 9075 Database Language SQL.

A minimal install, however, does not have all the functions of a DBMS such as Oracle, but you can choose what libraries (UDF and DLL) to add. See also https://www.ibphoenix.com/ and IBExpert Personal Edition, and excellent DB browser and manager.

Answer 2

You can do that in a RDBMS. I recommend Postgres. http://www.postgresql.org/. You can alternatively store the binary data on disk and use the database to store the file name.

Answer 3

It would be helpful, if you would clarify whether parent and child nodes are expected to form a tree-like structure or a network-like structure. Regardless of that, a system that you describe can be built, using pretty much any type of modern database systems or libraries: relational, object-oriented, multi-paradigm, hierarchical, network [those are rare] as well as document-oriented and other NoSQL database systems. It is just a matter of prioritizing or balancing requirements, features, development platform of choice, etc.

Obviously, if you'd be more specific about your data model, answers could be more helpful. For example, if your data model would be hierarchical, discussions like this and this would provide more concrete opinions or recommendations. Depending on your system's requirements, you may even do well by using some kind of a simple JSON storage (see NoSQL information via the corresponding link above).

The choice can also be impacted by the system's intended use (choice software with the right license might be tricky), a programming language (development platform) that you plan to use for the system as well as whether the system should support multiple platforms and which (both development and target).

Answer 4

Postgres

Postgres is a good choice as suggested in another answer.

License

Postgres is truly open-source and free-of-cost, with its own utterly simple "do anything but sue us" license.

Key-Value Store

Postgres has had a key-value store feature called hstore long before "no-SQL" became a buzzword.

And now Postgres has amazing new support for JSON data including indexing of nested elements, which you could treat as a whole new key-value store feature. For this use the newer JSONB data type rather than original JSON type. See this Question for discussion.

Quality

Postgres is known for its enterprise-quality, rock-solid stability, regular development cadence, security mindedness, and safe and sane engineering approach.

Nodes

I don't know what you mean by parent child nodes. Perhaps you just mean two tables in a one-to-many relationship. That is what relational databases are built for. Like invoice and invoice lines, or patient and doctor visits.

Java

Postgres is native code, written in C. So it is not Java and does not run in the JVM.

You use either of the two open-source JDBC drivers to connect your Java app. Choose either the classic driver or the "next-generation" driver rewritten from scratch to drop support for legacy versions of Java, JDBC, and Postgres.

Install

Postgres is a heavy install, creating a new OS-level user account "postgres" and creating folder hierarchy owned by that user. This is all for security. Uninstalling is possible but a bit of a chore. I suggest experimenting with Postgres inside a virtualizer such as VirtualBox, Parallels, or VMware Fusion so you can simply trash the VM file rather than uninstall.