Proper NoSQL data schema for web photo gallery

Question

I'm looking to build an appropriate data structure for NoSQL storage of a photo gallery. In my web application, a photo can be part of 1 or more albums. I have experience with MySQL, but almost none with key-value storage.

With MySQL, I would have set up (3) tables as follows:

photos (photo_id, title, date_uploaded, filename)
albums (album_id, title, photo_id)
album_photo_map (photo_id, album_id)

And then, to retrieve a list of the 5 latest photos (with album data), a query like this:

SELECT *
FROM albums, photos, album_photo_map
WHERE albums.album_id = album_photo_map.album_id AND
                photos.photo_id = album_photo_map.photo_id
ORDER BY photos.date_uploaded DESC LIMIT 5;

How would I accomplish a similar query using a NoSQL key-value pair database? (Specifically, Amazon's DynamoDB.) What would the storage look like? How would the indexing work?

Answer 1

Using mongodb lingo, your collections could look like this:

photos = [
    {
        _id: ObjectId(...),
        title: "...",
        date_uploaded: Date(...),
        albums: [
            ObjectId(...),
            ...
        ]
    },
    ...
]

albums = [
    {
        _id: ObjectId(...),
        title: "..."
    }
]

Finding the 5 newest photos would be done like this:

> var latest = db.photos.find({}).sort({date_uploaded:1}).limit(5);

There's no server-side joins in mongo, so you'd have to fetch all the latest albums like this:

> var latest_albums = latest.find({}, {albums: 1});

Of course, then you have to boil this down into a set.

It's actually easier if you just embed the album inside the photo documents, since they're small:

photos = [
    {
        _id: ObjectId(...),
        title: "...",
        date_uploaded: Date(...),
        albums: [
            {name: "family-vacation-2011", title: "My family vacation in 2010"},
            ...
        ]
    },
    ...
]

Then querying is the same, but you don't have to join. Finding all photos in an album looks like:

> db.photos.find({albums:{$elemMatch:{name: "family-vacation-2011"}}});

Answer 2

Redis can handle this. For the RMDBS table you mentioned above:

SET photos:photo_id:title "some photos title words"
SET photos:photo_id:date_uploaded "some uploaded time (2011-02-09 HH:MM:SS for example)"
SET photos:photo_id:filename "some filename words"

SET albums:album_id:title "some album title words"

SADD album_photo_map:photo_id album_id

Use a List(Redis suports list) to store last uploaded photos and update the list when a new photo is uploaded:

ret = r.lpush("upload:last_upload_times", photo_id) // update list
ret = r.ltrim("upload:last_upload_times", 0, N-1) // control list length

then, if we want to get the last uploaded N photos with album data:

last_uploaded_photo_list = r.lrange("upload:last_upload_times", 0, N-1) last_uploaded_photo_with_album_list = [(photo_id, album_id) for photo_id in last_uploaded_photo_list for album_id in r.smembers(photo_id)]