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)