Improving the database layout
Elmar
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
a
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.
EXIF
----
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
XMP
---
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.
Columns:
* Foreign key to "authors_positions"
* dc_creator
* photoshop_country
* photoshop_state
* photoshop_city
Here a further normalization may be possible (Countries, states,
cities)
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:
"xmp_subject":
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"
and
a "subjects" primary key