CODEWITHSUNDEEP
javaandroiddatabasesqliteorm
Mike76
Mike76

Reputation: 979

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 self-written object relational mapping class, then the person names are shown in the list.

Now I have to 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 separate 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.

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

Upvotes: 0

Views: 437

Answers (1)

CodeChimp
CodeChimp

Reputation: 4835

Relational databases are fine for this sort of use.

Where you query your persons table you need to change the query to include the count of items in the measurements table for each person. Something like this;

SELECT PersonName, Count(Measurements.ID) AS NumberOfMeasurements
FROM Persons LEFT JOIN Measurements on Measurements.PersonID = Persons.ID
GROUP BY Persons.ID, Persons.PersonName

That will give you two columns in your resulting cursor, the persons name and a count of measurements called NumberOfMeasurements. My SQL may not be exact but the count, join and group by are the important bits. Note that all fields you return from the Persons table must be included in the group by.

See this site for details

Upvotes: 4

Related Questions