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

Asked

Viewed 184 times

8

It is good practice to create a table to record all images from a database, or I can create columns that save image information for each specific need?

For example, let’s assume that I want to create a table for categories of a site and each category of the table has an image.

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

or is more correct like this?

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 best for good practice? Do you have any other suggestions? The second form seems to me more correct, but it is normal to have several images of categories, products, avatars of users, etc... All mixed in a table?

mysql sql database modeling

2 answers

7


Both ways may be correct depending on what you want.

Is the image part of the category information? Is there a 1 to 1 ratio between the category and image? That is, there are not several images for the category, nor can an image be used in more than one category? Readings (almost) will always need image information?

So it looks like the first one has nothing wrong, simplifies the process a lot. Separating this information into another table does not seem to bring any advantage 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 previous requirements, then it may be necessary to normalize the information. In fact as you have several specific information about the image, it may pollute a little the category table, but this should not be the criterion to adopt it. If the image works independently is a good reason to have it separate.

Then you have to ask yourself what is the advantage of using the second? What do you lose if you use the first one? Organizing by organizing without having a gain is not a good thing. Organizing should eliminate redundancy, should make information canonical. If this can be obtained 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.

  • Interesting answer. Yes, it is a ratio of 1 to 1, a category can only have one image. But I will add: In this database, there will not only be this table of categories with images. Are there other tables (products, users' avatars) that will need images, in this case it would be more advantageous to create a table to store all images? Or using a table for each need can still be more advantageous?

  • It still depends. If images are always an integral part of where they’re going to be used, and not independent, there’s no point in doing this. If the image is something independent and by chance will be attached to another item of the database, then it is interesting. It depends on the degree of associativity. You have to conceptualize well what you need to know how to implement. No one will be able to conceptualize the best for you unless you do all the lifting work. If everything will be a 1:1 me relationship, probably, put in the respective tables. Until I discover a reason to avoid this.

  • Cool. I liked your advice, I had thought about it, whether it would compensate to create a general table of images or not. I finally decided that I will not do this, because for example these images belonging to the category will not be used anywhere else but the categories. I marked yours as an answer, thank you very much!

  • Actually, you didn’t score.

  • Oxe, now scored, hahaha.

  • I liked your answer - but did you notice that from the second to the first example, various information is being flouted? This decides for me, first of all, which model should be used. Using the first template, he can never know when an image has been updated, because he has not this information - so simple.

  • I read @Thiagoyoithi’s question like this: Which is the best model, what I throw half my information away, or what not?

  • @Danielgomes can’t see what you saw.

  • You don’t read the date_created of tbl_image how the date the image was created? And date_created of tlb_category as the date a new record was entered in the category? I see 7 items against 11!

  • @Danielgomes but should be the same information, which even in the second would be redundant. But if it is not, if it can have a different date, then just create a column to save this date on the first.

  • And you think @Thiagoyoithi knows that? And he only committed one slip by duplicating all data? ;)

  • @Danielgomes there I do not know, but it is not difficult to perceive the time that is riding this.

  • 1

    Ahaha, I’m aware of that. I will think about how I will do, but you are right @Danielgomes, in the first way it is not possible to save the date when the image was created. However, this is not such useful information in the context of my application, as the image will be mandatory in the creation of the category. I think I can use the table very similar to the first form, keeping only the date of registration and update of the category.

  • 1

    @Thiagoyoithi I don’t think you really need it, but why can’t you save the date? Only if it’s because there’s no field. Then just create it, if it’s useful.

Show 9 more comments

4

The second form, definitely(1).

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

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 of Wikipedia.

(1) In his reply @Maniero refers to a relevant question that I initially did not take into account.
In fact, in the case of 1:1 relationships, it may not be useful to separate the image data into 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.
The BD generates a cache with the most frequently read data. Normally this cache is made at the row level and not at the column level. We would thus be caching data that is rarely needed.
This would become even more inefficient if the image were stored in a field BLOB

  • Hmmmm. I get it, I’m sorry I haven’t said anything before. You’ve had a lot of useful information in your comment, I’ll take that into consideration. In my application, the images are not stored in BLOB fields, because I am storing the images in Amazon S3 and saving only the file name in the BD. I believe the two forms have their advantages and disadvantages, I’ve made my decision.

Browser other questions tagged mysql sql database modeling

You are not signed in. Login or sign up in order to post.