A practical introduction to databases and data management

• describes the relationship between two or more elements in which each element uniquely identifies the other (likely to be true only in a specific context).
• represented by elements in the same row
• eg, name <–> mrn (but think about whether this is always true!)

Graphically:

And in our database:

select mrn, name from patients;

• in the context of a database, is represented by a pair of tables, in which an element or row can be related to multiple rows in another table

In our database:

select name, mrn, accession
from patients
join results using(mrn)
group by accession
order by mrn;

• an entity in table A maps to more than one entity in table B, and vice-versa.
• a many-to-many relation between two tables is represented via a third junction table or cross-reference table.
• for example, each patient may see more than one physician, and each physician sees many patients.

In our relational database, this relation requires three tables, one for each of patients and physicians:

And a junction table defining the many-to-many relationships:

select * from physician_patient;

Now we can view all physician-patient relationships using a three-way join

select *
from patients
join physician_patient using(mrn)
join physicians using(doc_id);

This query returns the entire table named results.

select * from results;

Use a where clause to specify a subset of rows.

select * from results where test_code = 'GLU' order by date;

You can also specify a subset of columns.

select mrn, date, value from results where test_code = 'GLU' order by date;

Relations between tables are specified using a join clause. Here's a two-way join between two tables, patients and results.

select * from patients join results using(mrn);

select patients.name, results.date, tests.test_name, results.value, results.flag
from results
join tests using(test_code)
join patients using(mrn)
where test_code = 'K';

select name, test_code, min(value)
from patients join results using(mrn)
group by mrn, test_code;

• When naming column headers, stick to lowercase letters and underscores only (numbers are ok after the first character).

• If you have multiple tables, be careful to name fields consistently (don't use "DOB" in one place and "birth_date" in another).
• If possible, don't use SQL keywords for column names.

Values in a column are expected to be the same data type. Some databases will produce an error if you try to mix data types (eq, putting a string in a column identified as an integer type). Others may attempt to coerce data to the same type, producing unexpected results. Even without an error, mixing data types greatly complicates writing queries. For example, how can we find values greater than 10 in this table?

In this case, it's better to create another column to contain the non-numeric values.

Excel and EMR applications have made us accustomed to viewing data as a square matrix.

This is great for some purposes: it allows us to easily compare the values of these two tests for this one patient over time. Or perhaps the square is transposed:

However, both of these are lousy formats for storing or manipulating data:

• they are likely to be sparse, where many cells are missing data, and requiring as many columns as there are dates (first table) or tests (second table) in the entire data set.
• adding data for an addition test or patient is cumbersome.
• it is not possible to associate additional attributes with each value.

Here's an improved representation of the above data:

This table has a fixed number of columns. Adding new data is as simple as tacking rows onto the bottom. We can annotate values with arbitrary attributes, such as methods or comments. It's even easy to aggregate data from multiple patients by adding another column.

Composing queries will be easier if you are consistent in your representations of the data. I've frequently come across data like this:

• within a column, pick a value and stick with it - it's awfully hard to write a query that accommodates multiple variants of the same term or concept.
• be consistent from column to column, as well. In the example above, we see both yes/no and +/- used as boolean values. How about this instead:

Here's where understanding relations is useful. When I ask for data from the laboratory system, I typically get results back that look something like this:

select results.*, b.battery_name, t.test_name, t.ref_range from results
join tests t using(test_code)
join batteries b using(battery_code);

(The data isn't actually stored like this in the database, but when the request for data includes all of the above fields, it winds up denormalized in the process of fitting it into a single output table).

In this table, test_code uniquely identifies test_name and ref_range, and battery_code uniquely identifies battery_name, so there's no need to include the last three columns in our table of results (imagine having millions of rows like this) - we can store these test and battery attributes in separate tables and perform joins as necessary.

It's worth having a look at the classic short article A Simple Guide to Five Normal Forms in Relational Database Theory

American humans might write today's date as 7/23/15, or perhaps 07/23/2015 or July 23, 2015; European humans might write it as 23 July 2015 or 23/7/15.

None of these formats are… (trying to be polite here) exactly optimal. For one thing, neither format results in alphanumeric sorting in chronological order. But much worse, it's hard to guess exactly what format the date is actually in, so parsing is a chore.

It turns out that there's a standard for representing dates and times that addresses these issues, and also allows the representation of time zone (which we won't get into right now): this standard is called ISO 8601. Using this standard, today would be written 2015-07-23. Please be nice and write dates like this. Follow the link to read more (you will learn that right now the ISO 8601 timestamp is something like 2015-07-23T16:55:02+17:00).