Masterguide DB Version, Tables, & Permissions
Welcome to PostgreSQL version 9.3. Among other advanced features, this database allows for native JSON querying.
Read more about the database here: http://www.postgresql.org/about/news/1481/
04/30/2014 - Initial Release
Series Tables, used to calculate the dashboard figures, and will allow us to eventually navigate to and from months in the Masterguide UI
Data Tables, extracted from Inteliguide/Patrolguide production data, transformed into a friendly state, and loaded into Masterguide
- businesses (also contains Franchises because they can serve as Businesses)
- group_locations (lookup table for Group Location members)
You can open the tables to reveal their data and design. Foreign keys for joining tables are self-evident and named appropriately.
For example: 'franchise_number' joins on the 'number' column in the franchises table. You'll see that a lot.
- Masterguide Tables (all except schema_migrations) are Read Only
- Cannot Create Users or Grant Rights
- Can create tables, including temp tables
- Can create views and functions
- Cannot create indexes
If you need new indexes created, or new users created, please submit requests to us here through Zendesk.
We recommend using Navicat Essentials for PostgreSQL.
You can also use PostgreSQL Admin for free.
Navicat Screenshot Example (in Mac version)
Navicat Query Options Example (in Mac version)
Build complex queries and save your work for later revision and reuse.
Navicat Export Options Example (in Mac version)
Export whatever dataset you're looking at with the click of a button.
Basic Examples using Navicat
All Active User Roles by Franchise
Joins between the 'users' and 'roles' and 'franchises' tables because any one user can have one to many roles, but only one role per franchise. This will mean that the same users will appear multiple times in the results set if they're authorized for more than one franchise market.
Just gimme all active users and their given roles per franchise:
SELECT u.first, u.last, r.role, f.number, f.name FROM users as u INNER JOIN roles as r on u.id = r.user_id INNER JOIN franchises as f on f.number = r.franchise_number WHERE r.archived is null ORDER BY f.number, u.last DESC --not specifying the direction defaults to 'ASC'
Returns 7125 records at the time of this example.
Ok, now let me see the total roles between active users and archived users:
SELECT 'active' as status, COUNT(*) FROM users as u INNER JOIN roles as r on u.id = r.user_id INNER JOIN franchises as f on f.number = r.franchise_number WHERE r.archived is null UNION SELECT 'inactive' as status, COUNT(*) FROM users as u INNER JOIN roles as r on u.id = r.user_id INNER JOIN franchises as f on f.number = r.franchise_number WHERE r.archived is not null
Returns 2 results displaying the active and inactive totals
Same thing a bit fancier written in one statement:
SELECT CASE WHEN r.archived is null THEN 'active' ELSE 'inactive' END as result, COUNT(r.id) FROM users as u INNER JOIN roles as r on u.id = r.user_id INNER JOIN franchises as f on f.number = r.franchise_number GROUP BY result
Returns 2 results displaying the active and inactive totals
Getting adventurous, let me see the totals as percentages:
SELECT COUNT(r.id) as total_authorized_roles, ROUND(SUM(CASE WHEN r.archived is not null THEN 0 ELSE 1 END)*100.00/COUNT(r.id), 2) as active_percentage, ROUND(SUM(CASE WHEN r.archived is null THEN 0 ELSE 1 END)*100.00/COUNT(r.id), 2) as inactive_percentage FROM users as u INNER JOIN roles as r on u.id = r.user_id INNER JOIN franchises as f on f.number = r.franchise_number
Returns 1 result showing the total amount of authorizations (roles) breaking out the percentage of active and inactive. This is one way of accomplishing it.
Feel free to run any of these examples to see the output and/or tinker with the variables. Just copy/paste the queries into your query browser of choice after you establish a connection.
Data Export Options using Navicat (an example)
Let's just dump all the data we want on any tour for any franchise ending in 4/29/2014 UTC. Here's the basic query to get at the data:
SELECT * FROM tours WHERE ended BETWEEN '2014-04-29 00:00:00' AND '2014-04-30 00:00:00'
Then we can easily use the Navicat export option to choose what type of file we want, step through the options, and save the results set to whatever local resource we want.
A Word About Dates/Times
All Masterguide Dates/Times are in UTC. This is because there needs to be a unified and standard way of storing all dates in the system across all timezones. Using SQL Date/Time functions, you can easily convert whatever Date/Timestamp you want to whatever local offset you desire. We recommend http://www.worldtimebuddy.com/ to get the offset from GMT that you need (GMT and UTC are synonymous for our purposes).
By way of example, let's modify the above tours export example and narrow our results to just those records that ended before midnight in CDT (considering we're in daylight savings time) on the 30th of April, 2014… for the Omaha market:
Since we know CDT is - 5 from UTC, we want to subtract that amount of hours from the 'ended' column in the 'tours' table before making the comparison… We also want to narrow the results to the Omaha market, which is 'franchise_number' 0.
SELECT * FROM tours WHERE (ended - INTERVAL '5 hours') BETWEEN '2014-04-29 00:00:00' AND '2014-04-30 00:00:00' AND franchise_number = 0 ORDER BY ended DESC
You can see that by adjusting the ended value before the comparison, we achieve the desired result. Sure enough, as in the example below, we see that the last tour was Completed a mere 35 seconds before midnight on the 30th when adjusted for CDT.
Documentation & Google is your friend!
You can learn all about any type of manipulation, operators, and functions, from your most basic to more advanced features, in the PostgreSQL 9.3.4 Documentation. Google is also a great resource if you're looking to do something fancy or complicated. There are great communities like https://stackoverflow.com out there that will help answer questions, and many similar questions to most things that you'd want to do with the data already exist for your instant edification.
Here's a link to the PostgreSQL 9.3.4 Documentation: http://www.postgresql.org/docs/9.3/static/index.html
And, as always, we're here to help. Start a conversation in the comments below, or submit a ticket!