The main question is how to organize storage:

  • photos of different sizes
  • videos of different sizes in several languages
  • multi-language audio

For example, there is a Π§Π΅Π»ΠΎΠ²Π΅ΠΊ object, you need to keep that person can look differently in photos, can speak several languages ​​and show how he can speak several languages ​​(there is no hard correlation with audio languages).

The photos of Π–ΠΈΠ²ΠΎΡ‚Π½Ρ‹Ρ… may be of other sizes, i.e. Photo size settings for different objects will be different. And audio and video settings too, for example, the length of audio / video for a Person should be longer than the length of audio / video for an Animal.

What is the easiest way to store all this in a database? And how to organize it in code? The language can be any (I have this PHP), the idea itself is more interested.

  • It is better to store binary information, of course, in files, and not in a database. and in the database, save uri β€” a unified resource identifier . - aleksandr barakin
  • this is understandable, the files will be separate from the database, in the database there will be only links to the files, this is not even discussed. the question concerns precisely the organization of the base - j. Doe
  • one
    It is unclear what problem you are solving. for example: the length of audio / video for a person should be longer than the length of audio / video for an animal - what relation can this have to the database and / or to the β€œcode”? - aleksandr barakin
  • This means that the configuration for media files is different for different objects. those. for a Man, photos of at least 100x300px are needed, and for an Animal at least 50x50px. It needs to be stored somehow in a code, a certain config for objects - j. Doe

1 answer 1

One of the possible options for storing media files can be the following: we organize the media table where we store all the files (photos, videos, audio, etc.) with the most general information about the file.

 Π’Π°Π±Π»ΠΈΡ†Π° media id - ΠΏΠ΅Ρ€Π²ΠΈΡ‡Π½Ρ‹ΠΉ ΠΊΠ»ΡŽΡ‡ media_file_id - внСшний ΠΊΠ»ΡŽΡ‡ Π² Ρ‚Π°Π±Π»ΠΈΡ†Π΅ media_class - имя класса Π² ΠΏΡ€ΠΈΠ»ΠΎΠΆΠ΅Π½ΠΈΠΈ (users, animals, audios, videos) filename - названия Ρ„Π°ΠΉΠ»Π°, ΠΏΡƒΡ‚ΡŒ формируСтся автоматичСски ΠΈ Π½Π΅ хранится Π² Ρ‚Π°Π±Π»ΠΈΡ†Ρƒ mime_type - MIME-Ρ‚ΠΈΠΏ Ρ„Π°ΠΉΠ»Π° size - Ρ€Π°Π·ΠΌΠ΅Ρ€ Ρ„Π°ΠΉΠ»Π°

The table has a composite foreign key ( media_file_id , media_class ), which refers to another table that already sets the class for the file. For example, users can be stored in the users table

 Π’Π°Π±Π»ΠΈΡ†Π° users id - ΠΏΠ΅Ρ€Π²ΠΈΡ‡Π½Ρ‹ΠΉ ΠΊΠ»ΡŽΡ‡ first_name - имя last_name - фамилия email - элСктронный адрСс nickname - Π½ΠΈΠΊΠ½Π΅ΠΉΠΌ

The link to the autark in the media table will contain the primary key user.id in the field media.media_file_id and the string 'users' in the field media.media_class . Styles like 50x50 can never be stored anywhere, but implied by default.

For a table of animals, you can create a separate table, each entry in which corresponds to one photo of an animal (file names are still stored in the media )

 Π’Π°Π±Π»ΠΈΡ†Π° amimals_photos id - ΠΏΠ΅Ρ€Π²ΠΈΡ‡Π½Ρ‹ΠΉ ΠΊΠ»ΡŽΡ‡ title - Π·Π°Π³ΠΎΠ»ΠΎΠ²ΠΎΠΊ author - Π°Π²Ρ‚ΠΎΡ€ Ρ„ΠΎΡ‚ΠΎ animal_id - внСшний ΠΊΠ»ΡŽΡ‡ для Ρ‚Π°Π±Π»ΠΈΡ†Ρ‹ ΠΆΠΈΠ²ΠΎΡ‚Π½Ρ‹Ρ… animal

The media table will contain the primary key amimals_photos.id in the media.media_file_id field and the string 'amimals' in the media.media_class field.

If there are any video materials, you can create a separate table with parameters for video

 Π’Π°Π±Π»ΠΈΡ†Π° videos id - ΠΏΠ΅Ρ€Π²ΠΈΡ‡Π½Ρ‹ΠΉ ΠΊΠ»ΡŽΡ‡ description - описаниС duration - Π΄Π»ΠΈΡ‚Π΅Π»ΡŒΠ½ΠΎΡΡ‚ΡŒ Π²ΠΈΠ΄Π΅ΠΎ

The media table will contain the primary key videos.id in the media.media_file_id field and the string 'videos' in the media.media_class field.

If there are any audio materials, you can create a separate table with parameters for audio files.

 Π’Π°Π±Π»ΠΈΡ†Π° audios id - ΠΏΠ΅Ρ€Π²ΠΈΡ‡Π½Ρ‹ΠΉ ΠΊΠ»ΡŽΡ‡ description - описаниС duration - Π΄Π»ΠΈΡ‚Π΅Π»ΡŒΠ½ΠΎΡΡ‚ΡŒ Π°ΡƒΠ΄ΠΈΠΎ

The media table will contain the primary key audios.id in the media.media_file_id field and the string 'audios' in the media.media_class field.