Photo Database Schema (2018-06-12)


Blog Post Published on:   26th October 2022
Title:   Photo Database Schema (2018-06-12)
Lead Author:   Fred M. Beshears
Type of Blog Post:   mycirclepines_website

Introduction

These notes on a photo database schema were written back on 12 June 2018. My thinking on the schema for the photo database has evolved considerably over the years, but I started writing down my thoughts back in 2018.

As an example of how things have changed since 2018, I’ve learned since then that one can use hash coding to easily find duplicate image files. Also, I’ve decided that it would be good to combine two functionalities into one web-app: 1) a photo sharing system that lets end-users upload and tag their own photos and 2) a photo-archive system that is managed by one or more system administrators. The photo records in the latter are supposed to be better organized and more consistently tagged. Finally, I’ve learned that tags can be used to informally express relationships such as one-to-many and many-to-many.

Anyway, here is what I was thinking back in 2018.

Database Schema for a Photo Database

Recently, I’ve been working on a database schema for a photo database project for an organization known as Circle Pines Center.

For those of you who may not know, a schema is a description of the tables in a database and the relationships between these table.

If you are interested in how one might set up such a database, then read on.

To get us started, here is my very professional schema drawing.

The basic tables in this schema are as follow:

  1. photos – a master table that provides a unique identifier for each photo in the database regardless of which album it might appear in. The actual photos are stored in a single folder (with file names like ‘0000001.jpg’). The unique identifier for this file would be ‘0000001’.
  2. tags – this table lists the tag names that might be assigned to a photo. As we’ll see later, there’s another table that will list which tag has been assigned to which photo. Also, these tags are not the same as the tags that result from tagging people in photos.
  3. folders – typically, photos start out in separate folders, which are often submitted by someone. For example, a friend of mine recently sent me a few folders containing photos. This table lists the folders that have been submitted. Later we’ll see how we can map from the photo’s master table to these folders. Also, note that albums that end up on face book are often created from folders, but there is no one-to-one match between the photos that come in a folder and those that end up in a given album. Sometimes I need to hold a photo back or remove a photo from an album because someone objects.
  4. albums – this table identifies the albums that have been uploaded to facebook.
  5. parties – for the most part, this table represents people. But, it can also be used to represent organizations. So, for example, many of the photos in the albums came from the archive of photos that I digitized back in 2007. Therefore, if we don’t know who took the photo then the party that ‘submitted’ the photo would be CPC. As we’ll see later, we’ll be able to map from photos to parties. Any given photo may be a ‘photo_of’ one or more parties. Similarly, a given party may ‘appear_in’ one or more photos.
  6. locations – this is a list of locations such as the farm house lawn. There may be other locations as well. Some photos are not of any person. For example, we have several photos of the old youth lodge and the rec hall.
  7. events – this will list events such as the 35th reunion in 1973. We know of at least some of the people who attended these events because we have photos of them at the event.

Intersect tables – these tables are used to show the relationships between the tables listed above.

  1. photo-tag – this table, for example, reflects the many-to-many relationship between tags and photos. Each row in this table will have the value that has been assigned to a tag key word for a particular photo.
  2. photo_party – this table reflects the many-to-many relationship between photos and parties (i.e. people and parties). So, a given person may ‘appear-in’ multiple photos, while a given photo may be ‘a_photo_of’ many people. Similarly, a given photo may have been ‘taken_by’ a particular photographer. And, a given photo may have been ‘submitted_by’ an organization such as CPC. These terms (e.g. ‘taken_by’ and ‘submitted_by’) are predicates. A predicate is the middle term in a sentence such as “This photo was submitted by Mara.” The term ‘this photo’ is the subject, ‘submitted by’ the predicate, and ‘Mara’ the object. In this database, we call a predicate a role_type in the intersect tables.
  3. photo_folder – the intersect table for a photos and folders. Note that in this intersect table, we also have a order_numstr field. We use this to impose an ordering of the photos in a folder. This number is also used to create file names for photos in folders (e.g. reunion_1998_001.jpg would be the name of a particular file in the folder named reunion_1998).
  4. photo_album – the intersect table for phots and albums on facebook. This also has a order_numstr field.
  5. photo_location – this intersect table reflects the many-to-many relationship between photos and locations.
  6. photo_event – this intersect table reflects the many-to-many relationship between photos and events.

Finally, here’s the schema in JSON, a form that the computer likes.

table_definitions_list_of_dict = [
    {
      'table_name' : 'photos',
      'field_names_types' : [
        ('id', 'integer primary key autoincrement not null'),
        ('file_number_str', 'text unique'),
        ('extension', 'text'),
        ('size_in_bytes', 'integer'),
        ('date_taken', 'text'),
        ('description', 'text')
      ]
    },    
    {
      'table_name' : 'tags',
      'field_names_types' : [
        ('id', 'integer primary key autoincrement not null'),
        ('kw_name', 'text unique')
      ]
    },
    {
      'table_name' : 'folders',
      'field_names_types' : [
        ('id', 'integer primary key autoincrement not null'),
        ('submitting_party_id','integer'),
        ('kw_name', 'text unique'),
        ('description', 'text')
      ]
    },    
    {
      'table_name' : 'albums',
      'field_names_types' : [
        ('id', 'integer primary key autoincrement not null'),
        ('kw_name', 'text unique'),
        ('title', 'text'),
        ('description', 'text')
      ]
    },
    {
      'table_name' : 'parties',
      'field_names_types' : [
        ('id', 'integer primary key autoincrement not null'),
        ('first_name', 'text'),
        ('middle_name', 'text'),
        ('last_name', 'text'),
        ('other_name', 'text'),
        ('photo_tag_name', 'text'),
        ('last_name_sort_str', 'text'),
        ('description', 'text'),
        ('phone_number', 'text'),
        ('email', 'text'),
      ]
    },
    {
      'table_name' : 'locations',
      'field_names_types' : [
        ('id', 'integer primary key autoincrement not null'),
        ('kw_name', 'text unique'),
        ('description', 'text')
      ]
    },
    {
      'table_name' : 'events',
      'field_names_types' : [
        ('id', 'integer primary key autoincrement not null'),
        ('kw_name', 'text unique'),
        ('title', 'text'),
        ('description', 'text'),
        ('date', 'text'),
        ('start_isodate', 'text'),
        ('end_isodate', 'text')
      ]
    },
    {
      'table_name' : 'photo_tag',
      'field_names_types' : [
        ('id', 'integer primary key autoincrement not null'),
        ('photo_id', 'integer'),
        ('tag_id', 'integer'),
        ('tag_value', 'text')
      ]
    },
    {    
      'table_name' : 'photo_party',
      'field_names_types' : [
        ('id', 'integer primary key autoincrement not null'),
        ('photo_id', 'integer'),
        ('party_id', 'integer'),
        ('role_type', 'text')
      ]

    },
    {
     'table_name' : 'photo_folder',
      'field_names_types' : [
        ('id', 'integer primary key autoincrement not null'),
        ('photo_id', 'integer'),
        ('folder_id', 'interger'),
        ('role_type', 'text'),
        ('order_numstr', 'text'),
      ]

    },
    {
      'table_name' : 'photo_album',
      'field_names_types' : [
        ('id', 'integer primary key autoincrement not null'),
        ('photo_id', 'integer'),
        ('album_id', 'integer'),
        ('role_type', 'text'),
        ('order_numstr', 'text'),
      ]
    },
    {
      'table_name' : 'photo_location',
      'field_names_types' : [
        ('id', 'integer primary key autoincrement not null'),
        ('photo_id', 'integer'),
        ('location_id', 'integer'),
        ('role_type', 'text')
      ]
    },
    {
      'table_name' : 'photo_event',
      'field_names_types' : [
        ('id', 'integer primary key autoincrement not null'),
        ('photo_id', 'integer'),
        ('event_id', 'integer'),
        ('role_type', 'text')
      ]    
    }

  ]