1

I'm looking for a good database to meet the following requirement:

  • Runs remotely (on Linux), single point of truth for multiple user
  • API for multiple languages (Java, C++, Python are a must) on different platforms (Linux, Windows) (Any SQL database fits here)
  • Can handle concurrent access (both read & write) from a few dozen users
  • The records I store are a mix of highly structured information and free text
  • Has to support versioning of records and should help creating nice diffs between versions
  • Also has to support actual deletion of data (not just replacing it with a newer version)
  • Supports different owners for records with some sort of ACL mechanism

An open source database would be preferred, but is not a hard requirement. I looked at Elasticsearch but role management and access security is not really convienient there. Postgres seems nice too, but I'm unsure how to use it to handle versioned data. So maybe you guys can share some experiences and insights to help me make a decision.

Edit: Let me clarify what I mean with versioning: The basic principle is that of a VCS like git. Each object (a file in git, a data type with some structured fields and an unstructured field in my case) is tracked. If I modify my object and store it into the database, I see two options (which keep the history). I do it like git and just store the diff, or I create a new record with the same ID, but a version counter is incremented. For the first one, it is trivial to query the diff to present it to the user. But the second one is probably more how most databases actually do it, and is easier to implement, both for the database as well for my client application.

Also, additional information about role management: The individual records I store are created by some user. Since multiple users do work with the database, multiple records of the same type can (and will) have different creators. Imagine a use case where only the owner/creator of a record is allowed to modify or delete it. This fine grained control should be available on record level (field level access security is not required).

Improve this question
2
  • The type of versioning you describe isn't natively supported by any common client-server database (Postgres, SQL Server, Oracle, MariaDB/MySQL, or Firebird, at least). However, you can implement this using an after insert or update or delete trigger on your tables. In Postgres, for example, I use a trigger function written in Python that pickles the row data before and after each change (no 'after' for deletions) and stores it in a single audit log table with some metadata. You might want to compute and store the diff instead.
    – rd_nielsen
    Feb 10, 2020 at 16:53
  • 1
    Postgres includes features for row-level security that might meet your needs for role management. See postgresql.org/docs/9.5/ddl-rowsecurity.html for details.
    – rd_nielsen
    Feb 10, 2020 at 16:55

1 Answer 1

Reset to default
0
  • Has to support versioning of record and should help creating nice diffs between versions
  • Supports different owners for records with some sort of ACL mechanism

Excluding the above two (because I did not understand what they mean) most known databases satisfy all the criteria. MongoDb, Postgresql, MySql, Oracle, and Sqlite, to name a few that I have worked with.

But working with a database is all about finding the one that is comfortable for you to work with your current knowledge of programming language. So to sum up just choose one database which you have worked with before and do it with your language of choice and if you get stuck somewhere stackoverflow is your friend.

Improve this answer
2
  • I edited the question to incorporate more information. Those two are quite important to my use case.
    – a.ilchinger
    Feb 9, 2020 at 15:01
  • Standard SQL brings row security features, and versioning should be done outside of the DB itself. Therefore, any relational database will do. Some offer some limited document storage capabilities which make life even easier when dealing with unstructured data. So I'll accept this answer, because it clearly highlights the essence of the question. Thanks.
    – a.ilchinger
    Feb 12, 2020 at 16:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.