photo.net custom field example

a Markman Tutorial in a case in the Eastern District of Texas.
by Philip Greenspun and John Morgan
A standard set of database fields were provided, e.g., exposure_date, caption, tech_details, but each photographer was able to add his or her own custom fields. Here are some examples from among the 10,363 rows in the table as of May 7, 2011: "Adjusted in Photoshop?", "Animal photographed in captivity", "Aperture", "Background Info", "Built-in flash?", "Cropped", "Exposure ISO", "Exposure Mode", "GPS Location", "Handheld or Tripod?", "Lighting", "Notes", "Polarization Filter used", "Province", "Region", "Scanned at dpi", "Scanner used", "Story behind photo", "Stylist", "Time of day (24 hr)", "Trying to figure out what this is. hope I can erase this", "Type of Bird/Animal", "White Balance", "Wife was hurrying me along", "Zoom Setting (estimate)". A photographer who added a custom field, e.g., "Adjusted in Photoshop?", would then have the option of putting in "yes" or "no" on every subsequent upload of a photograph. Thus the SQL schema or data model and therefore user interface was potentially customized for each of the thousands of users of the service, despite the fact that those users were not SQL or Web programmers and no programming work was done on their behalf (the system currently holds more than 4 million photos from more than 177,000 different photographers).

The data model fragment below shows how this is accomplished within the Oracle database. It shows the ph_custom_photo_fields table, which holds metadata about what kinds of custom fields are desired by different users. Based on information in this table, a _custom_info table will be created for each user who has chosen to customize the application. The core table in the application, ph_photos, stores the same information on each photo for each user.

-- metadata
create table ph_custom_photo_fields (
        custom_field_id         integer not null primary key,
        user_id                 integer not null references users(user_id),
        field_name              varchar(200),
        field_pretty_name       varchar(200),
        field_type              varchar(200),
        date_added              date,
        field_active_p          char(1) check (field_active_p in ('t','f')),
	field_comment		varchar2(4000)
);

-- A table ph_user_(user_id)_custom_info will be created to store custom photo
-- info.
-- It's columns include photo_id, data field's (being add on)
-- [SIC; grammatical errors in the above are in the original from 1999]

-- common table for all users
create table ph_photos (
	photo_id	integer not null primary key,
	user_id		integer not null references users,
	folder_id	integer not null references ph_folders,
	-- Can this photo be seen in the community
	photo_public_p	char(1) check (photo_public_p in ('t','f')),
	camera_id	integer not null references ph_cameras,
	film_id		integer references ph_films,
	file_extension	varchar(10), -- eg .jpg .gif
        ....
	exposure_date	date,
	caption		varchar(4000),
	tech_details	varchar(4000),	-- f-stop, shutter speed, film used
        ....

(full SQL schema is available here)

back to full tutorial