I have an image library where I wish to have the functionality for users to create boards from existing images in the MySQL database along certain themes. For instance they can create a board called 'London' and add photos of London that already exist in the library to that board.
In the boards table I intially couldn't work out how to store the images (effectively as an array) for each board, but having read some other answers it seems the best way to approach this is to create an additional table as well as the boards table?
There are currently tables that cover images and users (simplified below):
users table
- id -- username -- -
| 36 | Ted |
| 37 | James |
images table
- id ---- filename ------- - file_extension - -
| 1038 | bigben | jpeg |
| 1039 | toweroflondon | jpeg |
So, if I'm understanding this, I'll first have to create a boards table as per below
boards table
- id - board_name - -
| 1 | London |
| 2 | Trees |
...and then an additional table that consists of purely foreign keys: the id and board_name from the boards table, and the image_filename and file_extension from my images table, and the user_id of the person that set up the board which is the id from the users table.
images_boards table
- board_id - board_name - - image_filename - - file_extension - - user_id - -
| 1 | London | bigben | jpeg | 36 |
| 1 | London | toweroflondon | jpeg | 36 |
I'm struggling to get my head around this conceptually, it feels odd having a table made up of purely foreign keys, although this may well be perfectly acceptable / preferred solution in such a use case.
My question thus is - am I approaching this correctly ?
CodePudding user response：
A board can contain several images, and an image can be added to several boards. That's a typical example of a "many-to-many" relationship in a data model.
So your approach is good, but as Paul wrote in his comment you don't have to duplicate the content of the images table into the image_boards one. You only need a image_id column in it instead.
These keys are only used to store the relationship between all these entities. Then your queries are going to join the related tables to retrieve the additional information needed from each entity.
Also if you put user_id in this table, this should mean that a board that has been created by a user can be populated with images by other users. If it's not the case, then the user_id column should be in the boards table only. It would be useless to repeat it in every board/image relationship.
Finally, you will have to set a primary key for the rows of your images_boards table. There are generally two approaches for that:
- Either you create a new auto incremented column that would be the id of this relationship (for instance
images_boards_id). - Or you can use existing columns to compose a unique combination that can be used as a primary key. In your case if an image can't be added more than once within the same board, then the combination of
board_idandimage_idcan be used to compose a primary key.
CodePudding user response：
When structuring relational databases it's usually best to follow some level of normalisation. This makes it's easier to add more tables and restructure the tables. Read more about the different normalisation levels here in Wikipedia for example: https://en.wikipedia.org/wiki/Database_normalization
So in this case you would only need the foreign keys in the images_boards table.
Then you just join the tables when you want to fetch the data. Example that fetches the data for one board if the images_boards has the following columns: board_id, image_id and user_id.
SELECT boards.id AS board_id, board_name, images.id AS image_id, image_filename, file_extension, users.id AS user_id, username
FROM your_schema.images_boards
INNER JOIN your_schema.images ON (images_boards.image_id = images.id)
INNER JOIN your_schema.boards ON (images_boards.board_id = boards.id)
INNER JOIN your_schema.users ON (images_boards.user_id = users.id)
WHERE board_id = x