Created by IAN SALITRYNSKI (RIT Student), last modified on May 13, 2016

The following is a database schema diagram for the RocReadaR server. It takes into account Primary and Foreign Keys which are not present in Strongloop Loopback, and is a guide into the structure of the system, not a definition of it.

It can be accessed with the proper permissions here: https://drive.google.com/a/g.rit.edu/file/d/0B8ALJia9jVzAdUJfWUxRODg1ekU/view?usp=sharing

 

 

Metrics/Analytics

Several sets of metrics will be available. Most of these will be through the MediaAction table. 

The MediaAction table is linked to a piece of media. Every time an action is performed on this media, a new record can be added. Each MediaAction has a MediaActionType and a RocReadaR user. For example, if a user views an article, then shares a link, two MediaAction records may be added to the table. One with MediaActionType "ViewMedia" and another "FacebookSharing". The MediaAction records that it was the same user, on the same media, for the two types of actions, and the DateTimes of those actions.

This means that we will have to identify users of the RocReadaR App. This will be also important to record user location information. However, for initial release we can exclude this information, and leave the RocReadaR User ID fields blank.

The other metric that will need to be updated is the PublicationIssuePage table "TimesScanned". This field should be stored in memory in the instances of the recognition service as a count of the local updates, and added to the value in the database on a regular basis (maybe every 10 minutes). There should be concurrency controls on the database to make sure that multiple updates do not cause the data disappear.

Concurrency is not an issue for the MediaAction table. Only inserts should be done to the table, so multiple updates don't conflict.

These analytics are probably best queried in pure SQL. For example, to get a day by day record of facebook shares, you could do:

SELECT CONVERT(DATE, ma.TimePerformed) AS [Day], COUNT(1) AS [NumberShareMedia] FROM MediaAction ma
WHERE ma.MediaActionTypeID = (Facebook share media action type constant)
AND ma.TimePerformed >= StartDate
AND ma.TimePerformed <= EndDate
GROUP BY CONVERT(DATE, ma.TimePerformed)
ORDER BY CONVERT(DATE, ma.TimePerformed)

But let's spruce it up a bit, for a given publication:

SELECT CONVERT(DATE, ma.TimePerformed) AS [Day], COUNT(1) AS [NumberShareMedia]
FROM MediaAction ma
JOIN PublicationIssuePageMedia pipm ON ma.PublicationIssuePageMediaID = pipm.PublicationIssuePageMediaID
JOIN PublicationIssuePage pip on pipm.PublicationIssuePageID = pip.PublicationIssuePageID
JOIN PublicationIssue pi on pip.PublicationIssueID = pi.PublicationIssueID
WHERE ma.MediaActionTypeID = (Facebook share media action type constant)
AND ma.TimePerformed >= StartDate
AND ma.TimePerformed <= EndDate
AND pi.PublicationID = (selected publicationID)
GROUP BY CONVERT(DATE, ma.TimePerformed)
ORDER BY CONVERT(DATE, ma.TimePerformed)

Spruced up even more for all stats for a publisher:

SELECT CONVERT(DATE, ma.TimePerformed) AS [Day], COUNT(1) AS [NumberShareMedia]
FROM MediaAction ma
JOIN PublicationIssuePageMedia pipm ON ma.PublicationIssuePageMediaID = pipm.PublicationIssuePageMediaID
JOIN PublicationIssuePage pip ON pipm.PublicationIssuePageID = pip.PublicationIssuePageID
JOIN PublicationIssue pi ON pip.PublicationIssueID = pi.PublicationIssueID
JOIN Publication p ON pi.PublicationID = p.PublicationID
WHERE ma.MediaActionTypeID = (Facebook share media action type constant)
AND ma.TimePerformed >= StartDate
AND ma.TimePerformed <= EndDate
AND p.PublisherID = (selected publisherID)
GROUP BY CONVERT(DATE, ma.TimePerformed)
ORDER BY CONVERT(DATE, ma.TimePerformed)

Or an editor may want all stats over different publishers:

SELECT CONVERT(DATE, ma.TimePerformed) AS [Day], COUNT(1) AS [NumberShareMedia], p.PublisherID
FROM MediaAction ma
JOIN PublicationIssuePageMedia pipm ON ma.PublicationIssuePageMediaID = pipm.PublicationIssuePageMediaID
JOIN PublicationIssuePage pip ON pipm.PublicationIssuePageID = pip.PublicationIssuePageID
JOIN PublicationIssue pi ON pip.PublicationIssueID = pi.PublicationIssueID
JOIN Publication p ON pi.PublicationID = p.PublicationID
WHERE ma.MediaActionTypeID = (Facebook share media action type constant)
AND ma.TimePerformed >= StartDate
AND ma.TimePerformed <= EndDate
GROUP BY CONVERT(DATE, ma.TimePerformed), p.PublisherID
ORDER BY CONVERT(DATE, ma.TimePerformed), p.PublisherID

 

 

 

Attachments:

Database Schema.png (image/png)
Database Schema.png (image/png)
Database Schema.png (image/png)
Database Schema.png (image/png)