I started the application by first creating a
database within SQL Server to model albums, photos, and photographers, as well
as tag annotations mapped against them.
Here is the schema I ended up using for this
particular sample (I’ll be adding more properties as I expand the photo
management app more in future posts – but this is a basic start):
You’ll notice that I have an “Albums” and “Photos” table to
store common metadata associated with pictures. I am also using a
Photographers table to store photographer names. The Photos table has
foreign-key (FK) relationships against both it and the Albums tables.
I then created a “Tags” table to track tag annotations for
the photos. The Tags table has a foreign-key relationship to the Photos
table, and maintains a separate row for each Tag used on the site. One
benefit of this approach is that it makes adding new Tags super fast and easy
(you don’t need to worry about duplicate tag-name insert collisions, and you
can add and associate new tags in one database round-trip).
In addition to providing referential integrity, the
foreign-key from the Tags table to the Photos table also enables cascading
deletes – so that if a photo is deleted the tags associated will automatically
be deleted too (avoiding cases of “dangling tags” left in the database that are
no longer associated with the data they were associated against).