I want ask experienced software developers and architects about best architectural practices for the following problem.

Suppose we have two entities: Student and Teacher and each of these two entities has an image. How can i model the database? A single "Image" table with two nullable foreign keys (to reference the student or teacher table) or you prefer one table per entity (StudentImage and TeacherImage)

  • Why not a single Image table that doesn't have a link to either students or teachers and then those tables are linked to it. Or if many images are needed, then make a linking table. – VLAZ Mar 19 at 10:24
  • Lot of down votes and close votes for a question that has a good focus. Just because the question text isn't 3 pages long doesn't mean the question is bad or should be closed. – Greg Burghardt Mar 19 at 14:26

The final solution would depend on some additional factors, like if file storage is available, how large the images are and how the images will get used.

I would start with an Image table containing at least a unique key and either the image data or a filename for the image. The Teacher and Student tables would then each contain a foreign key referencing the Image table. If a Teacher and/or Student can have multiple images, then I would add separate mapping tables for each of them as needed. Those mapping tables can also hold additional information, like the ImageType (profile picture, cover, etc.)

If the Image table ends up having just the two columns, I would consider dropping that table and putting the image path/data directly in the Teacher and Student tables (or the mapping tables if multiple images are possible), but that also depends on if I foresee additional columns being added to the Image table at a later stage.

| improve this answer | |
  • it is a one-to-many relationship: a student or a teacher can have several images that is why I have to go through a separate table. I also have an "ImageType" column which can have the value "portrait", "cover". For the moment I have the same possible values ​​for a student or a teacher but that is likely to change – Saidi omar Mar 19 at 10:40
  • are you sure you want to keep the images in the database? why not the filesystem? do the images have different sizes/renditions? – lunadir Mar 19 at 10:52
  • @lunadir, was your question for me or for the OP? I prefer storing images on a filesystem (unless they are really small, like 16x16 icons), but I am not sure if you always have a filesystem available in cloud setups. – Bart van Ingen Schenau Mar 19 at 11:11
  • sorry, it was for the OP, i was kinda in a hurry; maybe a filesystem is not available, but if the db has to grow at least one could consider to put images in a dedicated nosql database; i don't know what the requisites of the architecture are, but i would try to anticipate all the common problems: maybe there will be thousands of images, maybe there will be permissions, maybe they will add redundancy, and so on; the more you give thought on this in the creation phase, the less you have to reengineer it after – lunadir Mar 19 at 11:53

I would suggest to put the image data in a central table for all objects and reference them with a forein key in your student and teacher tables. if you dont save the pictures directly into the database as binary data and put them in a file system, it might be a good idea not to save the full path of the file. because when you someday have to move the files from one directory to another, you have to edit all the database entries for the new path. I prefer to keep the absolut path where the pictures reside in a central configuration file of the application.

| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.