I have an image library where I would like to create the functionality where users can create boards of selected images. The boards will essentially be collections of images on the site along a certain theme e.g. a board called 'London' where you create a collection of images based on London (from images that are already in the image library).
The backend runs on PHP/MySQL and I seem to have a hit a problem I've never encountered before - how to store the images filenames as part of an array. Unique image filenames are generated when the images are initially uploaded e.g. 'sjkdwuiw18773.jpeg' and in the separate
images table, the filename and file extension each have their own column e.g. 'sjkdwuiw18773' and 'jpeg'. For the
boards table because these images are already stored in the database I can store the filename and file extension all as one string e.g. 'sjkdwuiw18773.jpeg'
When a user creates a board, the columns in the
boards table so far are the primary key
id of the board, the
board_name and a currently empty
board_images column. It's this third
boards_images column that I can't get my head around.
BoardsTable | id | board_name | board_images |
I'm very conscious that if this is done incorrectly there could be significant performance hit to the site, for instance if someone has a board that includes hundreds (or potentially thousands) of images, re-writing that array each time instead of just adding another value or values seems to be the wrong way of going about it?
Any help or suggestions greatly appreciated.
Note: Not using MySQL isn't an option.