Basic relational database concepts

Tables

Made up of rows and columns. Store collections of related data.

Subjects, Visits, and Forms tables.

Rows

Occurrences (records) of related data values in a database table. Each time you enter new data in a table, you create a row in that table.

Subjects, Visits, and Forms rows.

Columns

Occurrences (records) of individual data values in a database table.

The columns in the Subjects table might store a Subject ID, Subject Number, Subject Initials, Current Status, and First Screened Date for each subject (row) in the study.

Primary keys

Columns in a database table whose values uniquely identify a row in a table.

The Subject ID column in the Subjects table is a primary key because each Subject ID value can only occur one time in that table and is unique to each subject.

Foreign keys

Columns in a database table that establish relationships with other tables in the database.

The Subject ID column in the Visits table is a foreign key that allows you to create a relationship between the Visits table and the Subjects table as well as a relationship between the Visits table and the Forms table.

Joins

Expressions of relationships between database tables. A join occurs when you match the data value of a key (primary or foreign) in one table to the data value of a key in another table to retrieve related data.

To retrieve a visit and form data for a subject:

• Join the Subject ID value in the Subjects table to the Subject ID value in the Visits table.
• Join the Subject ID value in the Visits table to the Subject ID value in the Forms table.