PostgreSQL: From Idea to Database - Introduction
Databases are a critical component for nearly any modern application. The database is also a component that often is surrounded by confusion and apprehension.
This post is the first of the series PostgreSQL: From Idea to Database. The goal of this series to provide a guideline to how we approach database development at RustProof Labs. This includes covering both methodology and technology. This series provides working code examples, friendly explanations, and real-world database design scenarios. Hopefully this series helps explain and teach database design is a new, friendly format.
Project for reference
A major challenge with teaching database design is when you don't have a real project to use as an example. For this, I've chosen to use The PiWS project, an open source weather station designed around the Raspberry Pi. To read more about the PiWS, see my introductory post.
The PiWS project was chosen for a variety of reasons, but there are three main reasons:
- I know the code
- The project was developed in a way worth describing
- It's open source!
The third reason is a big benefit because the source code used in this series will come from the project's GitHub repository. This gives us a real project with real code to study how to approach the task of designing a database.
Throughout this series I provide examples of how the PiWS project was initially designed and how it has evolved.
Introducing the PiWS (Pi Weather Station)
The PiWS, short for The Pi Weather Station, is an open source, affordable weather station designed for everyone. This is not a traditional, commercial weather station that provides you a fixed set of sensors and collect data without providing you a way to keep your own data.
Instead, The PiWS is designed around open source hardware and software, provides you with a choice of sensors, and encourages long-term retention of your sensor data.
What does it do?
The PiWS collects data from a variety of analog and digital sensors to collect data from your local environment. When we talk about "environment", that could be indoor, outdoor, garden... or in the attic, basement, garage, or that one room upstairs that always seems cold.
The data from the sensors is collected in a PostgreSQL database on the Raspberry Pi, allowing you to store the data long term, analyze trends, or just satisfy your curiosity!
Why the PiWS?
The idea for the PiWS started around the time I started building TrackYourGarden (TYG) in 2015; it's another part of my desire to understand everything possible about our local micro-climate. I said at that time:
"It's hard to answer the questions of "When can I plant plant X?" living at 6,000 feet. We have a fairly short growing season; it has snowed on Mother's day two years in a row now, and it's not unusual to have at least one light snowfall around Halloween.... so naturally I figured the best thing to do was to gather some data (surprise!)"
GDPR Preparation: Google Analytics
This post was written after receiving an email from Google regarding our Google Analytics (GA) account. The email was received on 4/11/2018 with the subject line: “[Action Required] Important updates on Google Analytics Data Retention and the General Data Protection Regulation (GDPR).” This post includes a basic overview of the steps to take in your Google Analytics account to review and update your settings. The topic of data collection naturally leads into a discussion of data minimisation to round things out.
Philosophy
Data privacy is one of RustProof Labs’ core values. We believe GDPR is an example of moving the needle in the right direction in preserving and protecting our personal digital rights. Changes outlined in this document are in line with the opening statement in our Terms of Service:
RustProof Labs is a company committed to data, cybersecurity, learning, open source projects and are advocates for the right to privacy and security on the Internet.
This is another preparation for the approaching GDPR implementation on 5/25/2018 for anyone using Google Analytics. Our free Blog @ RustProof Labs, where you're reading this right now, receives hundreds of visitors each month from the EU. So, it is important that all RustProof Labs’ services, freely available blog included, are compliant.
Visualizing Colorado's Growing Population: 2010 to 2016
If you live in Colorado, you probably are familiar with a trend of quickly growing populations, more crowded traffic and hard-to-find homes. This post examines the change in population from 2010 to 2016 in Colorado using data from the American Community Survey. This analysis timeframe starts before the major boom really began and looks through the peak of growth in 2015.
... in 2015, when Colorado saw more than 100,000 new residents.
Media in this Post
The medaia content in this post (maps and charts) are intended to be visual by nature. The bar charts included provide tooltips on hover (or a tap on mobile) and look great on devices of all makes and sizes. Included above each chart is a link to open that chart in a new browser tab. This provides a better on-screen experience with data-heavy charts.
The maps included in this post were saved as high resolution (600 dpi) PNG images. To see the full details on each map, right-click the image in your browser and open the image in a new tab. This allows you to zoom and pan around the displayed area.
Security Incident - DNS Hijacking
This post is intended to provide an accurate record and notification about a Security Incident (SI) that affected a RustProof Labs owned and operated website. The SI was a case of DNS Hijacking that took place on 2/19/2018 starting around 9:00 AM MST and affected all domains under the trackyourgarden.com domain. The SI was resolved by RustProof Labs staff and normal site operations were resumed at 3:20 PM MST on the same day.
No RustProof Labs owned data, systems, or servers were affected or compromised.
What Happened
In the early hours of 2/19/2018, I logged into our domain registrar's website in order to renew a couple domain names, one of them being the domain for Track Your Garden. While I was logged in, I double checked a few settings, one of them being that the domains were locked against accidental change. The settings were set correctly as I expected, I logged out, and went about my day. A few hours later at 1:50 PM I discovered that all Track Your Garden domains were throwing an invalid TLS certificate error.
Mastodon