Given a Profile table containing details about the user - what are the best ways to store in the database the optional pictures they are allowed.
Every user has one main profile picture - an then three optional extra pictures. For sake of potential expandability and maintainability i thought maybe the three optional images could be stored as an array of some sort (~and this could also be a nullable field if they choose not to have any)
Please could someone advise on the best approach.
Thanks
You should create a separate table to hold the pictures, avoid creating a column that contains CSV data since you will need to split the CSV every time it is used. Not to mention that updating the column could be a nightmare.
create table picture(
picture_id int,
picture_file blob, /* If storing the file name use varchar*/
profile_id int /* FK to USER */
)
Then in the Profile table indicate the main profile picture via a foreign key to picture.
create table user(
profile_id int,
profile_picture_id int /* FK to main picture */
)
No, don't store them in array-
create a separate table instead and let your profileId be the foreign key for this table.
table images(
profileId int,
image varchar
)
