Improving the database layout

56 views
Skip to first unread message

Elmar

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

Elmar

unread,
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
Forward
0 new messages