How to build a database structure (SQL)?

Question

MySQL

For example, there are 4 types of files (image, video, audio, document), all of them have common fields:
* File size
* SHA512 hash
* Creation date
* The user who uploaded this file
And various fields specific only to a particular file type (required not null ):
Image :
* Height and Width
* Number of Colors
* Location
Video :
* Codec
* Height and Width
* Duration
Audio :
* Duration
* Sample Rate
Document :
* Document Format
* Can it be opened in a browser (boolean)
The question itself is : how to properly organize the structure of such a database?
Should it be one table? If so, how to validate the correctness of the entered data for a specific format?
Or should it be 1 (parent) + 4 under each type? Again, how to make sure that the record in the parent table does not have a link to two other records at once, or not one at all?

Answer 1

There are many implementation options. For example, the classical way of description:
Attribute table (id, name, attribute_type, attribute_required)
Variants of values ​​for select and multiselect (attribute_id, value_number, value)
Attribute membership table (document_type, attribute_id)
Table of attribute values ​​(document_id, attribute_id, value)
Validation should to deal with the client, in the database itself there are no funds for this.

Answer 2

It depends on the amount of data and the types of requests to it. Imagine your wildest fantasies (about the size of the database, of course), multiply them by ten. If more than a million rows did not work out, you can safely store it in one table by adding a sign of what kind of record this is, plus an index on it.