Improving the database layout

Skip to first unread message


Feb 24, 2010, 2:00:55 PM2/24/10
to JPhotoTagger Developers
JPhotoTagger uses a relational database: HSQLDB. Currently
JPhotoTagger's database mirrors the image's metadata - XMP and EXIF -
and JPhotoTagger does not change image metadata to reflect it's
database data. That leads to a bad normalized database layout with
these main disadvantages:

* Larger size (Megabytes)
* Slower queries
* Higher risk to get an "Out of memory" runtime exception

On the other side - if the images are the "masters" - insertions into
better normalized database would take more efford. Also it's not the
goal of a database layout to be normalized as high as possible due
decreasing performance (more table joins are neccessary).

The following draft tries to keep the balance between normalization
and practicablity.


1. In table "exif"
1.1. "exif_recording_equipment" as foreign key for a new table's
primary key. That table contains different recording equipment
(cameras, sanner e.g.)
1.2. "exif_lens" as foreign key for a new table's primary key. That
table contains different lenses


1. In table "xmp" move following data into a new table and
reference the records in it through foreign keys:
1.1 Table "recording_locations": Locations where an image
was taken. Moving these columns:
* iptc4xmpcore_countrycode
* iptc4xmpcore_location
1.2 Table "authors_positions", Column "photoshop_authorsposition"
1.3 Table "captionwriters", Column "photoshop_captionwriter"
1.4 Table "photographers" with photographers and their adderesses.
* Foreign key to "authors_positions"
* dc_creator
* photoshop_country
* photoshop_state
* photoshop_city
Here a further normalization may be possible (Countries, states,
1.5 Table "sources", Column "photoshop_source"
1.6 Table "credits", Column "photoshop_credit"
1.7 Table "rights", Column "dc_rights": Not clear:
Foreign keys to "photographers" or "credits" or
"sources" or to all of them or 1 varchar column?
1.8 Table resolving the n:m relation between XMP and DC subjects:
1.8.1 Rename table "xmp_dc_subject" to "subjects"
1.8.2 The new table "xmp_subject" has a foreign key pair to a "xmp"
a "subjects" primary key


Mar 14, 2010, 7:33:08 PM3/14/10
to JPhotoTagger Developers
Done in Version 0.8.0 2009-03-14.
Reply all
Reply to author
0 new messages