kingtommy dot com

PushProsRev data model

Work project · Table and column names are from the production schema

PushProsRev is the client-facing revenue dashboard I worked on at PushPros. This is the slice of its MySQL schema that carries a client's revenue from raw rows to the dashboard.

People and properties. A user owns properties through property.owner, and users_properties lets other users see them. Managers see their clients through users_managers. A property has two identifiers: its numeric id, and pd_id, the site id that every revenue and subscriber table uses as its key.

Revenue. daily_rev holds one row per property, revenue source and affiliate tag per day, with the revenue share already applied (revenue, revenue_client, revenue_pushpros, revshare). update_daily_rev_property() rolls those rows up into daily_rev_property, the table the dashboard reads for a property and a date.

Subscribers. property_subcount stores the daily subscriber count and new subscribers for each property.

The schema has no foreign-key constraints, so the application keeps these relationships. Solid lines are joins the code makes. The full schema has about 150 tables, including dated backup copies of the revenue tables. This is eight.

Entity diagram of eight PushProsRev tables. users owns property through property.owner, and users_properties and users_managers link users to properties and to other users. daily_rev rows, keyed by property and revenue source, are rolled up into daily_rev_property. property_subcount holds daily subscriber counts per property.
Eight tables from PushProsRev. Select to enlarge.

All demos