How in mysql to holistically link tables for each type of field?

Question

MySQL

In general, there is an entity in the database, it can be endowed with fields, respectively, the fields can be of different types text, datetime, int, and so on.
I decided that for each type of field - you need your own table, such as type_string, type_int, etc.
But I just can’t figure out how to link these tables with an entity using foreign keys so that integrity is preserved. How to define what table JOIN'it knowing type of a field?
I know this approach is definitely used in some CMS (shopscript, hostcms), but I didn’t figure it out, they made the choice of tables at the logic level or there is some kind of tricky relational model ..

Answer 1

Since the value of the field is by and large either int, or text or datetime, I decided to do this:
in the table with fields we make the field id and type, and in the field value table we make 4 fields: entity id, field id, value for strings, value for int. Thus, depending on the type of the field, we take the value from int or from text. I think this is not the most correct solution, but quite simple.

Answer 2

Something tells you that you are somewhat complicating your architecture.
Why do you need your own tables for each property?
As I understand it, you want to make a tricky entity in which any field can be of any of your "types" with a link to any table?) This, in my opinion, is not normalized at all ..
(well, most likely you can come up with something that works with triggers and procedures - but in my opinion it will be scary and unaccompanied)
Bring it to the application or pay attention to the nosql solution.
You can make an entity property table, with the property type and its value in the form of json, for example.

Answer 3

I assume you are doing something like CCK.
There are 2 approaches how to store such architectures.
This is what you wrote about. You store the fields as rows in a field table. For this you need 2 tables. One is the type. The other is fields. Where in the field table there will be a reference to the type. Thus, you can always select a list of fields of this type both for building a form and for displaying data.
You will need another table to store the articles and field data for that article. It's like a connection between a type and articles based on it. And already in the field data storage table, you can use different columns for different types.
Although I used one type and it works fast enough up to 100,000 entries in the section. But if it is more that it is necessary to define the types of course.
Naturally, each field is a PHP class or what you sew on there. And it just has a data type property. Based on this property, you know what to pull.
This is when you create a table for each type and add fields to this table as columns. Here you can get by with one table.
Both of them have their pros and cons. For example, with a horizontal approach, if your section contains several types, it is more difficult to select them and sort them by a similar property. And when it comes to filtering by fields, it's generally crap. On the other hand, it works a little bit faster. Well, mostly because you only work with one table at a time. On the other hand, if you also work in a vertical approach, then there is no difference in speed.
There are still some pitfalls. I did CCK for a CMS using a vertical approach. And so far I have not regretted it.