Good day. When developing a project, I wondered how best, from the point of view of correctness, to structure the database (MySQL engine MyISAM), namely, to store content in one or several tables?

I will give two examples.

Example 1 alt text

This example is taken from CMF MODx. Its peculiarity is that all content is stored in one table, except for user data, rights, etc. Entities (for example, articles, news, tags ...) are separated by the "context" field. Given the intensity with which news, tags, ... are added (there can be as many entities as you like), you can imagine how quickly the single content storage will swell (the "content" table). I understand that the number of records stored in the table is limited, only by the file system, but still.

Example 2

alt text

The difference from the first example is only in the fact that the "context" field is omitted, and the entities are separated "physically", i.e. according to the tables.

There is a conditional rule, if the fields of the tables are equivalent (the same), then merging several tables into one is welcome.

In favor of the second example, I would consider the possible sharding on specific entities in the future. To disadvantages: it was necessary to complicate the logic for implementing the functionality of displaying thematically similar material for a single article.

Dear Gurus, tell me which of these two solutions is preferable in your opinion?

    4 answers 4

    Personally, I would, people who invented the first version, would have battered their hands for a long time. You have 2 different! entities, data about which it is advisable to store in different tables. As they say, cutlets separately, flies separately.

    • @masterMind Agreed. Apparently in CMS, on the basis of security, adding / deleting tables in the admin panel is impractical, because the carrier of the entity is a record (in my example, it is an entry in the "context" field). - romeo
    • This is how to look. In some situations it is more convenient to consider “news” and “articles” as different entities, in some as one. I would not be so categorical. In the end, besides flies and cutlets, there is Ockham with his razor. - MrClon
    • Tell us how you will do a full-text search for articles and news, sorted by relevance. without attracting sphinx and other dances with a tambourine (such as union or or and and or a temporary table) ... / This is all about the fact that you need to approach the task wisely, and not "it’s here." - Yura Ivanov

    The first course and better and more convenient. A sharding will need to be done on the volumes, when the table contains the millions of records. And it is better to do it not by the type of entity, but by id (even - in one database, odd in another).

    Slightly more about sharding .

      I put everything in a separate table:

      news - id,parent news_name - id,parent,name news_title - id,parent,title news_content - id,parent,content
      • love join'y and multi? it is impossible to live like this, especially since it is difficult to come up with an excuse for such an approach. - Yura Ivanov

      There is such a thing as database normalization . It is one of the fundamental when designing any database. About this, you should at least know and understand why this is done.

      ZY Yes, in some cases, deliberate denormalization of the database is used, but certain conditions must be created for this.

      • one
        I am not strong in the theory of the relational data model, but as far as I understand the idea of ​​normalization, she has nothing to do with it. Although knowing about it, working with the RDBMS, of course, is necessary. - MrClon