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
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
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