Dealing with stale data persistence

Question

Currently implemented the model and working on the UI for a scheduling application. This is just an in-house application for work between ~5 people that may occasionally have the application running at the same time.

It will be run from the network and use a SQLite database (I understand the concurrency issues and feel that it won't be an issue for our use). It will look something like this:

+------------------------------------------------------------+
|       |1/1/2018 | 1/2/2018 | 1/3/2018 | 1/4/2018 | 1/5/2018|
+------------------------------------------------------------+
|Jack   |Available|Leave     |Leave     |Available |Leave    |
|Jim    |Available|Available |Available |Available |Available|
|John   |Leave    |Leave     |Available |Available |Available|
+------------------------------------------------------------+

The model works correctly with a console app. Currently implementing the views & viewmodels now. One issue that I've thought about for a few days is what to do about stale data?

Eventually I will implement a background thread that will check the database at user set intervals for a refresh. This would work fine for reads, but as I speak to below, doesn't this negate an in-memory collection in my model?

However, I'm not sure what to do about writes. When a user makes a change to someone's availability, I assume I want to persist that data immediately so other users can be informed. But doesn't this negate the collections in the model (and the model really), if I just persist directly from the viewmodel to the database?

Additionally, what do I do if two users are making changes and I get into a race condition where the 2nd one makes a write using stale data because he never received the update from the first user?

-> User clicks drop down for Jack on 1/1/2018 
-> Drop down displays list of  availability options (e.g. leave, available, unavailable, etc.)
-> user selects different availability option
-> updates viewmodel property with new selected availability
-> updates Jack's schedule in the model 
-> ??? Persist to database immediately and/or do I even need a collection of schedules?

I'd rather do this myself and handle these issues as I get to learn a lot by doing it, so I'd rather not use a ORM framework such as Entity.

Answer 1

When a user makes a change to someone's availability, I assume I want to persist that data immediately so other users can be informed. But doesn't this negate the collections in the model (and the model really), if I just persist directly from the viewmodel to the database?

I assume you're working from an entity in your Model. This entity contains your most recently updated data from the database, based on your update interval. The ViewModel just maintains a ("cached") collection of such entities. Persistence has to occur eventually.

What do I do if two users are making changes and I get into a race condition where the 2nd one makes a write using stale data because he never received the update from the first user?

Perform a read immediately before you write, so that you know you have the most current record.

Answer 2

I think that one boon you'll miss without an ORM framework is cache eviction strategy. For example a cache can be simply Identity map, which you'll use to place your collection in code (just a global hashmap which evicts data according to predefined algo). But as you say you won't use Entity or sonething, you'll have to do the bookkeeping : create a globally accessable hashmap which has listeners on your read/write operations (or write only - you decide according to eviction logic) and go for regulated access for models and persistence layer. You can also use ActiveRecord pattern where model and persistence are tied together, which is not my favorite thing. These are my 4 cents.