How to properly design a MySQL database?

Question

MySQL

For the first time engaged in database design.
1. Please point out obvious errors and flaws.



2. Also interested in how best to organize comments. Make separate comment tables for each type of publication (news, post, special, special_page), or add a unique comment identifier to each publication (following the example of services like disqus, only your own).

Here is a zip -archive with a higher resolution image, sql file and project file for MySQL workbench.

Answer 1

it is not clear why to have 4 tables for similar entities - post, news, special, special_page. If they are united, then immediately the question with the comments will fall off.
IMHO it’s a very bad practice to have faceless id, name - it’s better to have blog_id, post_name and everything is immediately clear without looking at schemes or keys

Answer 2

Pay attention to your INT (6), INT (3), INT (1), I think that this is not quite what you wanted (try to write the number 1000000 into INT (1)).
You use VARCHAR everywhere, although you can (and should) use CHAR for password and salt.

Answer 3

I'm in the tables of links (for example, post/tag ) I make the key one additional field with autoincrement.

Answer 4

I will also contribute. I would:
1. Refuse the abbreviated naming of the fields and name some fields differently bid -> blog_id, uid -> user_id, uid_add -> created_by, uid_upd -> updated_by, time_add -> created_at, url_name -> slug
2. Combine the post, special, special_page, news tables (and accordingly, instead of 2 news_tag & post_tag tables, there would be one) + as already mentioned, the issue would be resolved with comments
3. For the status, I would use ENUM instead of INT (1), and if ENUM is not possible, then at least TINYINT
:) level doesn't seem to help)

Answer 5

At a minimum, we see here a bunch of internal, it is not clear what the variables are responsible for.
so until there is a normal technical specification with a description of what is needed and how you see the solution to this problem - your diagram will not tell 90% of users anything

Answer 6

Too many tables

Answer 7

It's hard to say without TK. It seems to me that the functionality is quite standard and you can get by with the USER and PUBLICATION tables. If the publication has a link to the parent publication, then this is a comment, and then you can build them, even ribbons, even trees. Still, of course, you need to have a TAGS table, but it is not yet clear what will need to be built from it (maybe related arrays of relevance), then it is difficult to say a hundred something definite about its structure. Why the HURRY table (and related tables) is needed is not clear at all.
PS By the way, what is the diagram drawn with?

Answer 8

Relevant: eax.me/database-design