This is going to be a long article about photography and databases. Mostly databases, and in particular how to model certain photographic entities in a relational database. If you’re not interested in databases, stop reading now!
I have quite a collection of cameras and lenses and I wanted to track as much information as possible in a database, and keep it as normalised as possible. To further complicate the situation, I also use various films to produce negatives, and then I scan these in and make prints on various types of paper – but we’ll ignore the films, negatives and prints for now.
The underlying premise of the cameras and lenses is quite straightforward. Cameras and lenses are joined by a mount. For example, any Canon FD lens will work on any Canon FD camera.
However, when we start to list extra attributes surrounding cameras and lenses, suddenly it gets more complicated. Each of these sections describes a different problem and a few notes on the compromises I made. I’m trying to balance good normalisation with ease of use, especially as I don’t have a frontend application for this database – all querying and editing is either done via the MySQL command line or using MySQL Workbench. Throughout this article, table names are capitalised and field names are lower case.
Fixed lenses
As I hinted at in the ER diagram above, there is a table called MOUNT which lists all the interchangeable camera mounts available and allocates a mount_id. It’s then easy to add a mount_id to each row in CAMERA and LENS.
However, some cameras (e.g. compacts) have fixed lenses. Do we model this by using a virtual fixed mount, or by describing the lens directly in the camera?
Initially, I started out by defining fixed mounts as rows in MOUNT which had a boolean fixed which was set to 1 for fixed-lens cameras with fixed mounts. It’s not possible to have just one fixed mount called “fixed” – you need a different fixed mount row for each fixed-lens camera, so you can distinguish which lens belongs to which camera. As I have quite a large number of fixed-lens cameras I ended up with a large number of rows in MOUNT and I decided that approach was unsuitable.
I now set a boolean in CAMERA called fixed_mount. If fixed_mount is true, I set lens_id in CAMERA. If it’s false, I set mount_id in CAMERA instead. It’s not ideal, but at least I can determine programmatically whether or not a camera has a fixed lens, and which lens(es) it is compatible with.
Shutter speeds
On some cameras, the available shutter speeds are a property of the camera – e.g. most 35mm SLRs. On some cameras, the shutter speeds are a property of the lens (e.g. most large format cameras and some medium format cameras). How do we model this?
As most of my cameras have the shutter speed set on the camera rather than on the lens, I’ve opted to record available shutter speeds in CAMERA and to ignore (for now) the cases where the shutter speed is set on the lens.
This isn’t ideal, though. Imagine a situation where I want to run a query to check which of my cameras has the fastest shutter speed. I’d need to query both the CAMERA and LENS tables – and at the time of writing the LENS table doesn’t even have this information. I’m not sure how to handle this.
Film format
In most cases, it’s possible to say definitively which film format a camera uses – e.g. 35mm film, 120 film or 5×4″ film. However, some cameras can make more than one kind of film – e.g. the Horseman 45HD which can take 5×4″ and 120. Some cameras can only take one kind of film, but can shoot more than one size of image on that film – e.g. the Mamiya RB67 which takes 120 film but can shoot 6×7 or 6×4.5.
Just to complicate things more, in some cases different types of film come up with the same size negative, e.g. medium format 120 and 620 film which can both take pictures in 6×4.5, 6×6, 6×7, 6×8, 6×9, 6×12 or 6×17.
This led me to implement a table called FORMAT which only records the type of film used (35mm, 120, 126, 620, 5×4, etc) and another table called NEGATIVE_SIZE which records the size of negative taken. In most cases, records in FORMAT and NEGATIVE_SIZE correspond directly, but not for medium format, as mentioned above.
For each record in CAMERA, I log the format_id for the most common format the camera is capable of using (so I’d use 120 rather than 620 if a camera can take both) and I log the negative_size_id for the largest negative the camera can take – so I’d log 6×9 rather than 6×6 for the Ensign Ranger which can take both. Potentially I could use a link table to comprehensively define which film formats and negative sizes a camera can use, but this sounds even more complicated.
While I think I’m modelling the data quite well in this way, it is too fiddly for my liking so I’m thinking of combining FORMAT and NEGATIVE_SIZE into a single table which would describe both attributes, although there would be some loss of normalisation, e.g.
- 35mm
- 120 (6×6)
- 120 (6×7)
- 120 (6×9)
- 620 (6×6)
- 620 (6×7)
- 620 (6×9)
Manufacturer
There is a table called MANUFACTURER which simply lists manufacturers of camera equipment. Each row in CAMERA and LENS has manufacturer_id set to correspond to the manufacturer of that equipment. This in itself is very simple, but the difficulty comes when you want to print out a camera and lens combination at the same time – which row do you grab from MANUFACTURER?
Joins are required to retrieve the manufacturer of the camera and the manufacturer of the lens separately. This example probably isn’t great as the manufacturer of both is Canon – but this isn’t necessarily the case (I also have Sigma, Tokina and Tamron lenses for my Canon SLRs).
MySQL [photography]> SELECT
-> concat(cm.manufacturer, ' ', c.model) AS CameraType,
-> concat(lm.manufacturer, ' ', l.model) AS LensType,
-> n.shutter_speed,
-> n.aperture,
-> concat(n.focal_length, ' mm') as FocalLength,
-> FROM
-> FILMSTOCK as fs,
-> NEGATIVE AS n
-> INNER JOIN FILM AS f ON n.film_id = f.film_id
-> INNER JOIN CAMERA AS c ON f.camera_id = c.camera_id
-> INNER JOIN MANUFACTURER AS cm ON c.manufacturer_id = cm.manufacturer_id
-> LEFT JOIN LENS AS l ON n.lens_id = l.lens_id
-> LEFT JOIN MANUFACTURER AS lm ON l.manufacturer_id = lm.manufacturer_id
-> WHERE
-> f.filmstock_id=fs.filmstock_id
-> LIMIT 10;
+------------+-------------------------+---------------+----------+-------------+
| CameraType | LensType | shutter_speed | aperture | FocalLength |
+------------+-------------------------+---------------+----------+-------------+
| Canon T90 | Canon FD 50mm f/1.4 | 1/125 | 8.0 | 50 mm |
| Canon T90 | Canon FD 50mm f/1.4 | 1/125 | 8.0 | 50 mm |
| Canon T90 | Canon New FD 24mm f/2.8 | 1/60 | 5.6 | 24 mm |
| Canon T90 | Canon New FD 24mm f/2.8 | 1/30 | 5.6 | 24 mm |
| Canon T90 | Canon New FD 24mm f/2.8 | 1/15 | 5.6 | 24 mm |
| Canon T90 | Canon New FD 24mm f/2.8 | 1/60 | 4.0 | 24 mm |
| Canon T90 | Canon New FD 24mm f/2.8 | 1/125 | 4.0 | 24 mm |
| Canon T90 | Canon New FD 24mm f/2.8 | 1/750 | 4.0 | 24 mm |
| Canon T90 | Canon New FD 24mm f/2.8 | 1/125 | 5.6 | 24 mm |
| Canon T90 | Canon New FD 24mm f/2.8 | 1/60 | 5.6 | 24 mm |
+------------+-------------------------+---------------+----------+-------------+
10 rows in set (0.01 sec)
Sheet film negatives
Well, I initially said this post was going to be about the cameras and lenses in my database, but this point is about negatives. Let me explain.
A camera is used to shoot a film, which contains several negatives. This works quite nicely when shooting 35mm film (36 exposures) or 120 film (10 exposures) as both of these fit into an A4 film sleeve and can be filed in my ringbinder. The record in the FILM table has information about how the film was developed: developer, time, temperature, whether the film was pushed/pulled, etc.
This model breaks down a bit when using 5×4″ large format sheet film. Each negative is one sheet of film and as such can be processed differently from any other – you can’t model several sheets of film as different negatives in the same film. So now I’m shooting large format, each film as defined in the FILM table contains only one negative as defined in the NEGATIVE table. However, one A4 sheet of large format sleeves holds four sheets of film and therefore, according to the new model, four films.
This displeases me, because each A4 page in the binder should hold only one film. But it’s wasteful to fill a four-pouch negative sleeve with just one negative. Worse yet, I might shoot two large format negatives, then some 35mm, and then another two large format negatives – so the numbering won’t even be continuous! It’s the right way of modelling the data, though.
Jonathan Gazeley 