Have images in the tables where they will be used or table with repository of images?

Navigation
8

Is it a good practice to create a table to record all the images in a database, or can I create the columns that save the image information for each specific need?

For example, let's suppose I want to create a table for categories of a website and each category of the table has an image. 

tlb_category
- id
- title
- img_name
- img_ext
- date_created
- date_updated
- status

or is this more correct? 

tlb_category
- id
- title
- img_id
- date_created
- date_updated
- status

tbl_image
- id
- img_name
- img_ext
- date_created
- date_updated
- status

Which is better for good practice? Do you have any other suggestions? The second form seems more correct, but it is normal to have several images of categories, products, user avatars, etc ... All mixed in a table?

    
asked by anonymous 25.10.2015 / 16:04

2 answers

7

Both forms may be correct depending on what you want.

Is the image part of the category information? Is there a 1 to 1 ratio between category and image? That is, there are not several images for the category, nor can one image be used in more than one category? Will the (almost) readings always need the image information? So it seems that the first one is nothing wrong. It simplifies the process a lot. Separating this information into another table does not seem to bring any benefit and is not even doing normalization.

The second is interesting if you are working with an image repository, or if it does not meet the above requirements. Then it may be necessary to normalize the information. In fact as you have several specific information about the image, it can somewhat pollute the category table. But this should not be the criterion for adopting it. If the image works independently it is a good reason to have it separate.

Then you have to ask yourself the advantage of using the second one? What do you miss if you use the first one? Organizing by organizing without having a gain is not a good thing. Organize should eliminate redundancy, should make the information canonical. If this can be achieved with the first form, go with it, it will be more performative and simpler to deal with it. If you understand that there is or may be a loss in the future, go with the second.

    
25.10.2015 / 16:27
4

The second form, definitely (1) .

An image is an image, regardless of what it represents. What is not "normal" is to mix information (fields) relative to the category with information (fields) relative to the image.

This has to do with the concept of normalization :

  

Normalization is the process of organizing the columns and tables of a relational database to minimize data redundancy.   Normalization involves the decomposition of a table into less redundant and smaller tables without loss of information.
  (...)
Translation from Wikipedia.

(1) In your answer, @Bigown refers to a relevant issue that I did not initially consider. In fact, in the case of 1: 1 relations, it might not be useful to separate the image data in another table. However, in the likely scenario where category data is much more often accessed than image data, it may be more efficient to do so. BD generates a cache of the most frequently read data. Usually this cache is done at the line level and not at the column level. We would thus be caching data that is rarely needed.
This would still be rendered more inefficient if the image was saved in a BLOB     

25.10.2015 / 16:20
Semantic Doubt html5 How does the BLOB field work?