Database design - best practices [closed]

I am creating a website where the user has the possibility to upload content (pictures, videos pdf's etc.).

Each type of content has a different number of properties with corresponding values.

I have thought about making this design:

Is this the right way to do it?

Since each type of property has a predetermined set of property-names, should I make a relation between the two? Or would it be fine to handle this in code instead?

Solution 1:

Normally in a relational database you should use columns to model the attributes of an entity. So "Plan A" is to create PictureContent, PDFContent, etc tables in the database.

The design you are proposing is called Entity-Attribute-Value or EAV. It has some pretty serious drawbacks, but it is occasionally useful, typically when data sizes are moderate and you need change your metadata at runtime.

If you do need a dynamic schema, consider using JSON instead of EAV, which can easilly be serialized into .NET objects, or accessed dynamically.