Schema

Below is the basic layout of the tables. Its a pretty simple schema in that there are users, products and purchases. There's a few specific items to Postgres within the schema which are walked through below. For the tables themselves:

hStore

hStore is a key-value store within Postgres. It allows some of the flexibility of schemaless, with the data guarantees that come with a reliable battle tested relational datastore in Postgres. hStore in this example data is used for various user information that is optional and may or may not be captured. This would allow us to quickly experiment with capturing certain demographic information to determine if we should target certain segments, then if needed better integrate this into our schema.

Array

Arrays have long been a datatype in Postgres since 7.1. When it comes to a relational database, arrays are just like, well arrays in anything else. You have an array of a datatype and are able to store data into it and access it by its index. In the example dataset we use arrays as if it were the shopping cart for our purchase. This saves us from having to manage two tables for the purchase in this example app.

Example Queries

A large part of the data in this datatypes is highlighted in both the data stored, but also in the flexibility of retrieving the data using this various datatypes. To highlight the first example lets take a look at computing the total of all purchases for all time.

Since purchases have an array of all the items, the quantity and price we have a UDF to compute the total for a given row. This UDF looks like:

Additionally, we've stored the sex of users that we have it for. In this case it may be an optional field, or something we've only recently started tracking. We're using an hStore on the users table for this in a column called data. For this case its sex, but essentially we're using it for any generic data we want to associate with a user. Based on this we can compute the total of purchases for Males, Females and unknown with: