I have a situation where i have two categories. Lets say type A and type B. In my database I have some 50,000 type A entries and 100,000 type B entries as of now. For each entry mentioned above(type A or type B), there are three featured galleries(lets say gallery a, gallery b, gallery c) and many other galleries (lets say galleries 1-n). So basically we have many galleries and many images. lets say each ID in type B have 100 images tagged for it, then there would be around 10M images roughly. Total size of all images on disk is < 1TB. I have few servers load balanced to serve these files.
The constraints i have are
I shouldn't have any duplicate images and each image should have some unique id based on which its path/location on disk is calculated.
As I have galleries everywhere, I need to find a uniform logic for the user to navigate from one picture to next picture.
for each id in type A or type B i should be able to query the latest 4 images in featured galleries a,b,c and latest 4 galleries from galleries (1-n). This is the query that has to be optimized for.
Now the tricky parts here is there are complex relations between some of the images(something like tagging into several galleries). Its hard to explain, so i will try to explain using an example. lets say a user uploaded an image into id=400 in type A and he wants this image to be displayed in id=400 in type A -> gallery a, id=400 in type A ->gallery 4, id=600 in type B gallery b, id=600 in type B -> gallery 10.
i am breaking my head on how to design my database in an efficient way solving this problem. I was also researching if any nosql database would be best fit for my situation but couldn't come to any conclusion. since there are multiple relations for each image, i was thinking to tryout ne04j and see if it helps. Please help.