Spreadsheet

About

This page documents work related to an overhaul of the Databrary 2.0 spreadsheet interface.

Data model

This section represents an informal attempt to capture the data model that underlies the Databrary spreadsheet. There are three main tables, Session, Person, and File, with two linking tables SessionPerson, and PersonFile.

Session

flowchart TD
  A[Session] --> B(session_id)
  A --> C(date_time)
  A --> D(location)
  A --> E(type)
  A --> F(volume_ID)

Figure 1

Person

flowchart TD
  A[Person] --> B(person_ID)
  A --> C(birthdate)
  A --> G(age_entered)
  A --> H(age_units)
  A --> D(gender)
  A --> E(race)
  A --> F(ethnicity)

Figure 2

File

flowchart TD
  A[File] --> B(file_ID)
  A --> C(file_type)
  A --> D(date_created)
  A --> E(permission)

Figure 3

PersonFile

flowchart TD
  A[PersonFile] --> B(personFile_ID)
  A --> C(person_ID)
  A --> D(file_ID)

Figure 4

SessionPerson

flowchart TD
  A[SessionPerson] --> B(sessionPerson_ID)
  A --> C(person_ID)
  A --> D(session_id)

Figure 5

Combined

flowchart TD
  A[Session] --> B(session_id)
  A --> C(date_time)
  A --> D(location)
  A --> E(type)
  A --> F(volume_ID)
  B --> H[SessionPerson]
  H --> I[Person]
  I --> J(person_ID)
  I --> L(birthdate)
  L --> R(age_calculated)
  I --> S(age_entered)
  C --> R
  J --> K[PersonFile]
  K --> O(file_ID)
  O --> Q[File]
  Q --> M(type)
  Q --> N(permission)

Figure 6

Here’s an alternative that uses Mermaid’s entity-relationship (erDiagram) syntax.

erDiagram
  SESSION ||--o{ PERSON : depicts
  SESSION {
    string sessionID
    string sessionDate
  }
  PERSON {
    string personID
    string birthDate
    string gender
  }
  SESSION ||--o{ FILE : contains
  FILE {
    string fileID
    string fileType
    string filePermission
  }
  PERSONFILE ||--|| PERSON: links
  PERSONFILE ||--|| FILE: links
  PERSONFILE {
    string ID
  }

Figure 7: Entity-relationship diagram for Databrary spreadsheet model.

Let’s try another that depicts the SETTING separately.

erDiagram
  SESSION ||--o{ PERSON : ""
  SESSION {
    string sessionID
    string sessionDate
  }
  PERSON {
    string personID
    string birthDate
    string gender
  }
  SESSION ||--o{ FILE : ""
  FILE {
    string fileID
    string fileType
    string filePermission
  }
  PERSONFILE ||--|| PERSON: ""
  PERSONFILE ||--|| FILE: ""
  PERSONFILE {
    string personFileID
  }
  SETTING ||--o{ FILE: ""
  SETTING {
    string settingID
    string settingType
    string lat
    string lon
    string city
    string state
    string county
    string country
  }

Figure 8: Entity-relationship diagram for a possible Databrary spreadsheet model.

Finally, let’s consider MEASURES as separate from PERSON.

erDiagram
  PERSON {
    string personID
  }
  MEASURE {
    string measureID
    string measureName
    string measureType
    string measurePermission
  }
  PERSONMEASURE |{--|{ PERSON : ""
  PERSONMEASURE |{--|{ MEASURE : ""
  PERSONMEASURE {
    string personMeasureID
  }

Figure 9: Entity-relationship diagram for a possible Databrary spreadsheet model focusing on individual properties of measures.

Finally, we could consider a BIDS-inspired scheme:

https://bids.neuroimaging.io

Commentary

• Consider thinking about sessions as containers, as DB1 did or as collections of people and files.
• Consider desig