Photo gallery DataBase structure

Question

So, I'm building a large scale photo gallery and I'm a bit puzzled when it comes to building and structuring the DataBase. Having little experience with noSQL DB's, this seems to be a big step up.

Important to mention, that the DB will hold only url ref's to the photos, which will be stored in a cloud.

Basically, I want each user to have a few photo albums, and in each album around 3000 photos. I want to let the user filter each album fast and efficiently, but no more than one album to filter (meaning he cant search all his photos at once).

My 2 main question here are:

1. Which will be more suitable- SQL or noSQL?
2. Storing photos:
   • Should I store photos per album, meaning giving each albums an array field, which will include 3000 photo objects.
   • Or should I store photos as a separate collection/table and ref each to its album?

Keep in mind filtering efficiently is a high priority. Any specific DB recommendation will highly appreciated :)

Thank you

Answer 1

I would think that you would want a SQL database that supports binary objects for this such as MariaDB which is quite efficient for online/web applications. I would guess the basic database structure would be something like this :-

create table ALBUMS (
    user_id     integer,
    album_id    integer,
    album_name  text
    )

create table PHOTOS (
    album_id    integer,
    photo_name  text,
    photo_data  blob
    )

Obviously you will want to think about keys and indices to make this more efficient and no doubt you will have additional meta data to add as extra columns. This assumes that the albums do not have a fixed order for the photos. If they do you will need a column for that and will want to SORT BY that column in your select statement.