5

Trying to create a single field that holds two types of data is not something I would recommend. It would make it impossible to have a foreign key to the members table. I think the two column approach is the best way to go. I would consider using a view here that unions the join against the members table and the ad hoc names. That will remove the ...


4

The best way to guarantee that you stay reasonably decoupled from the database, but at the same time remain free to use any feature of it, is to not create an abstraction layer for the database. (Well, unless you have the explicit requirement now, that you need to support multiple databases. Otherwise YAGNI.) The worst thing one can do, is to try to stay "...


2

I assume that H2 will get a SHARE lock when querying the database, and needs a WRITE lock to add new rows the table, which it cannot do whilst there is a SHARE lock on a table. That is only true if you're using the older PageStore engine. If you are using anything more recent than H2 version 1.4 then MVStore is the default engine. That allows concurrent ...


2

It's very unlikely that you could restrict the access to a single table in a real application, so you would have to specify all the required tables and maintain that list. It's a somewhat normal approach to limit things to the database user on the larger scale, such as using different roles for reporting, normal users, admins etc. depending on which layers ...


2

Basically yes: We are aware of no other embedded SQL database engine that supports as much concurrency as SQLite. SQLite allows multiple processes to have the database file open at once, and for multiple processes to read the database at once. When any process wants to write, it must lock the entire database file for the duration of its update. (source) ...


1

Why not let a third "DatabaseLoggger" service update the database? So there will be only one app writing to the database, and none of the other apps is coupled directly to the db. All those other apps will have to send logging events to the service.


1

You need to only mock the database connection underneath your functions. If you are using dependency injection (or even a service locator) you should be able to swap the database connection out for a mocked one quite easily.


1

Why would you wish to create such a degree of abstraction? The very reason that different storage technologies exist, is because each have a matrix of advantages and disadvantages that cannot be made general - not even by the best minds of the industry and the economic resources of massive corporations. If any storage technology would seem to be ...


Only top voted, non community-wiki answers of a minimum length are eligible