Tabular view of data from normalized SQL database

Question

I've tried creating a normalized database, but I'm having trouble displaying the data in a tabular format.

In my example below, the database is used to track numbers (in various, arbitrarily-named categories) by date. As an example, a user may track how many fruits and vegetables are delivered to his grocery store each day. The user defines the names of the fruit and vegetable categories, and how many categories are present. Here are the tables corresponding to this example:

A tracker table:

 id |      name       
----+---------------------
  1 | Grocery deliveries 
  2 | Sports cars

An entries table:

 id |      datetime       | tracker_id 
----+---------------------+------------
  1 | 2013-10-01 00:00:00 |          1
  2 | 2013-10-02 00:00:00 |          1
  3 | 2013-10-03 00:00:00 |          1

A values table:

 id | number | entry_id | category_id 
----+--------+----------+-------------
  1 |   10.0 |        1 |          1
  3 |   20.0 |        1 |          2
  5 |   21.0 |        1 |          3
  7 |   18.0 |        2 |          2
  8 |    4.0 |        3 |          1
  9 |    9.0 |        3 |          2

And a category table:

 id |     name        | tracker_id 
----+-----------------+------------
  1 | Tomatoes        |          1
  2 | Carrots         |          1
  3 | Brussel sprouts |          1
  4 | Ferraris        |          2

I'd like to print out a table for tracker 1, with each row corresponding to a date (no repeated dates). The columns would be: date, category 1 (Tomatoes), category 2 (Carrots), category 3 (Brussel sprouts). If there was no value for a given category on a given date, it would be empty or show as null. So, ideally, it would look something like this:

 datetime            | Tomatoes | Carrots | Brussel sprouts
---------------------+----------+---------+-----------------
 2013-10-01 00:00:00 | 10.0     | 20.0    | 21.0
 2013-10-02 00:00:00 | Null     | 18.0    | Null
 2013-10-03 00:00:00 | 4.0      | Null    | 9.0

I'm not sure how to do this, or if there's a better way to store this data. Any suggestions?

It was easy to display the data when the entries and values were represented by a single table (entries were rows, values were columns). But in that case, the maximum number of categories was limited by the number of columns in my table. I prefer how the normalized approach allows each "tracker" to represent an arbitrary number of categories.

Answer 1

I came up with an alternative using PostgreSQL's crosstab function, as suggested by @PM77-1.

Specifically, I'm using the crosstab(text source_sql, text category_sql) form of the function, as follows:

SELECT * FROM 
  crosstab('SELECT e.datetime, v.category_id, v.number 
            FROM entries e, values v 
            WHERE v.entry_id = e.id AND e.tracker_id = 1 ORDER BY 1, 2', 
           'SELECT id FROM categories WHERE tracker_id = 1 ORDER BY 1')
  AS (row_name timestamp without time zone,
      tomatoes numeric,
      carrots numeric,
      brussel_sprouts numeric);

With this approach, the AS (...) term must be unique for each tracker, since the number of categories and their names may be different for each tracker. In my case, I'm executing the query using Python and the psycopg2 module, so it's straightforward to generate the query dynamically. For instance,

# Retrieve the category names for the current tracker
cur.execute("SELECT name FROM categories WHERE tracker_id = " + 
            str(tracker_id) + ";")
categories = cur.fetchall()
category_count = len(categories)

# Generate category string
cat_str = '';
for n in range(category_count):
    cat_str = cat_str + ", cat_" + str(n) + " numeric"

cur.execute("SELECT * FROM crosstab("
            "'SELECT e.datetime, v.category_id, v.number FROM entries e, values v"
            " WHERE v.entry_id = e.id"
            " AND e.tracker_id = " + str(tracker_id) +
            " ORDER BY 1, 2;',"
            " 'SELECT id FROM categories WHERE tracker_id =" + 
            str(tracker_id) + "')"
            " AS (row_name timestamp without time zone" + cat_str + ");")

results = cur.fetchall()

The result has generic column names cat_0, cat_1, etc. instead of tomatoes, carrots, etc.. However, I pass both categories and results to an HTML template to render the table with the correct headings.

Answer 2

Here's how I would define your tables:

deliveries
    id          unsigned int(P)
    good_id     unsigned int(F goods.id)
    qwhen       datetime
    quantity    double

+----+---------+------------+----------+
| id | good_id | qwhen      | quantity |
+----+---------+------------+----------+
|  1 |       1 | 2013-10-01 |     10.0 |
|  2 |       2 | 2013-10-01 |     20.0 |
|  3 |       3 | 2013-10-01 |     21.0 |
|  4 |       2 | 2013-10-02 |     18.0 |
|  5 |       1 | 2013-10-03 |      4.0 |
|  6 |       2 | 2013-10-03 |      9.0 |
|  7 |       1 | 2013-10-01 |      3.0 |
| .. | ....... | ...........| ........ |
+----+---------+------------+----------+

good_types
    id                  unsigned int(P)
    name                varchar(50)

+----+-------------+
| id | name        |
+----+-------------+
|  1 | Groceries   |
|  2 | Sports cars |
+----+-------------+

goods
    id              unsigned int(P)
    good_type_id    unsigned int(F good_types.id)
    name            varchar(50)

+----+--------------+-----------------+
| id | good_type_id | name            |
+----+--------------+-----------------+
|  1 |            1 | Tomatoes        |
|  2 |            1 | Carrots         |
|  3 |            1 | Brussel Sprouts |
|  4 |            2 | Ferraris        |
| .. | ............ | ............... |
+----+--------------+-----------------+

And here's the SQL to get the column names:

SELECT id, name
FROM goods
WHERE good_type_id = 1

+----+-----------------+
| id | name            |
+----+-----------------+
|  1 | Tomatoes        |
|  2 | Carrots         |
|  3 | Brussel Sprouts |
+----+-----------------+

And here's the SQL to get the data to show in the table:

SELECT qwhen, good_id, sum(quantity) AS total
FROM deliveries d
LEFT JOIN goods g ON d.good_id = g.id
WHERE good_type_id = 1
GROUP BY qwhen, good_id

+------------+---------+-------+
| qwhen      | good_id | total |
+------------+---------+-------+
| 2013-10-01 |       1 |    13 |
| 2013-10-01 |       2 |    20 |
| 2013-10-01 |       3 |    21 |
| 2013-10-02 |       2 |    18 |
| 2013-10-03 |       1 |     4 |
| 2013-10-03 |       2 |     9 |
+------------+---------+-------+

So then you'll use PHP, Java or whatever your high-level language is to loop through the two query results to display the data. Below is PHP code to display the data and below the PHP code is an image showing you what is displayed.

// Get the column headers
$sql = 'SELECT id, name FROM goods WHERE good_type_id = 1';
$stmt = $pdo->prepare($sql);
$stmt->execute();

// Start our table.
echo '<table border="1" cellspacing="0"><thead>';

// Print out the headers.
echo '<tr>';
echo '<th>Date</th>';
while ($row = $stmt->fetch()){
    echo '<th>'. $row['name'] .'</th>';
    $columns[$row['id']] = $row['name'];
}
echo '</tr>';
echo '</thead><tbody>';

// Get the data.
$sql = 'SELECT qwhen, good_id, sum(quantity) AS total FROM deliveries d LEFT JOIN goods g ON d.good_id = g.id WHERE good_type_id = 1 GROUP BY qwhen, good_id';
$stmt = $pdo->prepare($sql);
$stmt->execute();

// Manipulate the data into an array.
$save_date = NULL;
while ($row = $stmt->fetch()){
    if ($save_date !== $row['qwhen']){
        $save_date = $row['qwhen'];
        $data[$row['qwhen']] = array();
    }
    $data[$row['qwhen']][$row['good_id']] = $row['total'];
}

// Print out the table data.
foreach ($data AS $date => $cell){
    echo '<tr>';
    echo '<td>'. $date .'</td>';
    foreach ($columns AS $id => $name){
        echo '<td align="right">';
        if (isset($cell[$id])){
            echo $cell[$id];
        }else{
            echo '&nbsp;';
        }
        echo '</td>';
    }
    echo '</tr>';
}

// End our table.
echo '</tbody></table>';