Hi,

As the title suggests this is a design question.

Imagine this simple example. I have a File entity and every time someone would view the file that would be considered to be a visit.

I have two options to count the visits
1. the strainght forward way, keeping a file_visit table that will create a record everytime when the file is visited. That implies that in order to get a list of files I'd need to run a count as a formula thing. Visits are not going to be ever displayed or used of a different purpose but counting.
Avantages:
- clean and simple solution
- works well in clustered env.
Disavantanges:
- can create problems if items are very popular they would have a zillion of entries in the visit table hence a degradation of performance.


2. Maintaining a visits field in the File table and incrementing it when the item would be display/downloaded. THis sounds easier than is done.
Advantages
- increased performance, no additional db calls
Disadvantages
- can create issues in clustered enviroment with respect to the synchronizing the updates of the field while the file is viewed in the same time.

A common question, how is this working with a cached entity. I'm using a second level cache and I'd refresh this number since all other people are using it.

What are your thoughts? How is the best way of doing this fairly simple solution.


Regards,
Q
http://www.goodstockimages.com

Use the <version> element, it's designed for this. Also specify select-before-update="true" on your class mapping, for extra safety.

Well isn't that plain wrong ? I have a version in File but that is for optimistic locking if I load before update I'm basically overwritting others users changes and that is wrong.

Another idea is to have a file_visits with one-to-one relationship with File table so I'd have only a record for each File. There I can use the suggested solution since that table will only have (id, version, visits_count ) on it.

Definetelly I can do that...

Regards,
Q

If you are using <version> for locking then you can't use it for hit counting, it's true. You can use <version> on a one-to-one cascade="save-update" related entity, or you could simulate what <version> does, as in your original option 2.

You shouldn't have to worry about multiple access, etc. If it's likely to be a frequent occurrence, or if the value is critical and you can't risk it being wrong, then adding select-before-update="true" to the class mapping will work for you. Especially if you don't provide the setHitCount method in your entity's interface (put it in the entity implementation and mapping, just not in the public interface), so that only your data access code changes it. So when the data access layer is saving the entity, it starts a transaction, refreshes the entity to get the latest hit count, then increments it before saving. No business logic or application code involved, so a much reduced chance of the value getting out of sync.