vendredi 25 septembre 2015

Implement something like reference counting in SQLite database

The problem concerns two SQLite database tables: persons and measurements.

One person can have many measurements, but each measurement has only one person. So it is a 1:M relationship between person and measurement.

I already have a list which shows all persons. The database entities from the persons table are converted to person objects by my custom object relational mapping class, then the person objects are shown in the list.

Now I have expand the list to show not only the person names but also the number of measurements for each person.

One list entry should look like this:

Person XY
2 Measurements

One approach would be to make a COUNT query for each person just before the list is shown. However, this would be a really inefficient and bad solution.

Another approach would be to add a ref_count column to the persons table, which has to contain the number of related measurements.

I could also store an ArrayList of measurements for each person using a binary large object, but that feels like a hack.

If feels like a relational database has a bad structure for problems like this. How should I implement the list?

Aucun commentaire:

Enregistrer un commentaire