I am writing a blog, now I am writing a wall, I am interested in this question. What should be the structure of the database for storing messages of the wall, when registering, create a table where messages will be stored for each user?

Update Thank you all

  • four
    The structure should be what you need. And it depends on the functionality of your blog and the perversity of your imagination (regarding the functionality of the blog). In order not to get smart answers, please write the real problem that you encounter when developing the database structure. - Alex
  • I have not yet created a table for messages, a little earlier I created one table where all the messages were stored, and I chose them and output them by login, but in this case there will be a lot of records in one table, and if you create a table for each user, then There will be a lot of tables. I'm just learning, and now I want to find out how to make it as convenient and fast as possible when there are a lot of users. - O.Vykhor
  • @ O.Vykhor, as Alex said above, it all depends on what functionality you want to reward your blog, the usual query for 3-4 columns with data in the form of Int and varchar, with added indices, takes no more than 2 seconds in a sample of 500,000 records (at me). You’ll probably have page navigation on your blog, you’ll probably want to use Ajax. So it all depends on the planned scalability and load. But I gave the minimal, and simple initial structure in the answer - LamerXaKer

3 answers 3

In outline:

#users id | login | password | screen_name #posts id | alias | author_id | date_posted #posts_content post_id | language_id | title | post_body #comments id | post_id | author_id | text | date_posted | parent_comment_id

With the user table, I think everything is clear ( screen_name is the username displayed).

Users can create posts. Posts are stored in the posts table, where author_id is the identifier of the user who created the post, which refers to an entry from the users table. The content of the posts (i.e., headers, text in different languages; possibly metadata) is stored in the posts_content table. In principle, this data can be stored in one table, i.e. combine posts and posts_content (for example, if it is planned that posts will be only in one language, without translations). Comments are stored in the comments table, where post_id is the entry identifier from the posts table, author_id is the entry identifier from the users table, parent_comment_id is the identifier of the "parent comment" (used if you want to display a comment, as an answer, to another comment).

A little more about comments.parent_id : selecting and displaying a hierarchical comment structure may not be such a trivial task. If you take it to implement, I advise you to familiarize yourself with: The use of recursion to display the tree structure and http://www.getinfo.ru/article610.html .

Creating a table for each user is not the best idea, as for me. A large number of entries in the table should not scare you, but when creating a table, take care to arrange the indexes: at least index post_id and author_id .

I also advise you to read the documentation on almost any framework. There, almost everywhere, the creation of a blog (like "Hello World" in programming languages) is going on as an introductory exercise; the data structure is usually also discussed in general terms.

    Well, you probably need at least 2 tables: Пользователи(user_id, nick, password) and Сообщения(id, title, text, id_blog(id этой же таблицы), user_id, date) and then, you can choose different with different queries. For example, to begin with, we do a sample of all Id in which Id_blog is not installed, which means it is the basis of one or another blog, and output blogs, and then, when opening each particular, we search for the blog id , entries for which id_blog is equal to this value and this will of this blog.

    • I did something similar already, but when there are a lot of records there it will take a very long time to load - O.Vykhor

    This is a tough question for which there are several questions.

    Using mysql (implementation bd) and a simple structure as in the response of Igor Karpenko, which in principle will cover your functionality.

    Pros:

    • data integrity

    Minuses:

    • with the expansion and expansion of the functionality of the project, the complexity of introducing a new

    Not realizable database. You simply create the collection you put into it and get the same as they were when it was saved.

    Pros:

    • No structure

    • Easy implementation of new functionality (data storage for it)

    Minuses:

    • No structure (you have to control the integrity of the data in the code)

    And the last is a combination of two architectures of the real and non-realizable databases. Those. use mysql where data structure and integrity are needed, and noSql (mongoDB for example) where data integrity is not important, and there is processing of this data and bringing them to a specific storage structure in the real-time database. (for example, it is all sorts of trees, articles, etc.)