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

Figure 5

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