How to properly design a database?

Question

Database

When developing a new project, the question always arises of how to correctly develop a database in MySQL so that it can handle loads well and be easy to use. For example, there are users, there is contact information about users, a company, linking companies to users, transactions, accounts, etc. ) maybe there is a technique, article, software?

Answer 1

1. We select all the necessary entities (objects)
2. We define a set of fields for entities
3. After the entities are created, we normalize them - we need to bring them to at least the 3rd form
4. Again we look into those. task and roughly throw in possible queries, select heavy queries (those that pull a lot of data, or that take a lot of tables) and try to remake the database structure so that queries are simplified. Here's a stop: most often the main queries are SELECTs, but it happens that SELECT is done relatively rarely and a quick INSERT of data is much more important, you have to dance from here.
5. Again we critically evaluate the structure, and again we throw out sample requests. We carefully look at INSERTs. We look at which INSERTs can be put into triggers. We put on an exemplary body of the trigger, straighten the structure under it
6. Again we critically evaluate the structure, we look at what we are missing. We are trying to move part of the SELECTs to the View, and some to the stored procedures
7. Again, we critically evaluate the resulting mess. We fix something, remember that we missed something else. We return to point 3.
8. After it seems to us that everything is done, we describe each table and field in words, describe in detail what we did and why, where we optimized it and for what.
9. The next morning we look again at our structure, we are horrified. We remember that yesterday we made a description. We read carefully, we understand that we missed something else. Fixing, adding.
10. We bring the structure to the neighboring programmer and try to explain the flow of data to him. When everything finally comes to him, we ask him what he could do. He gives advice, after which you will invent that "it was obvious after all . " We redo everything again.
11. When the neighbor programmer got tired of you and sent the fuck agreed with your structure, open the database administrator, throw in a diagram, put down the keys. Again we understand that we missed something. Let's redesign the structure.
12. Exhausted and angry, you go to bed. Morning is wiser than evening :)
© me

Answer 2

There is a lot of material on this topic even within the Runet. But first, it is desirable to read about normalization and functional dependencies. Examples of good design can be seen in open source projects such as bugzilla.

Answer 3

We were taught in CCEP like this:

• Subject area. We describe (better not in the mind) in simple Russian - what we are modeling. A database is always a model of something in reality. The main objects and connections between them are enlarged, without details. Tool - MS Word, notepad, paper pen brain.
• We create a conceptual model. You can already draw pictures in Word.
• Next comes the logical data model - it already has embryos of future tables, with field names and relationships between them. Here we already indicate the type of relationships - 1:1, 1:n, m:n. Here you can already draw in a simple graphic editor or on the board.
• Next, the penultimate stage is the physical data model , when we specify the field types. At this stage, the least painful thing is to use UML diagrams, for example, using StarUML.
• And the last but cyclic stage is data normalization . Find out what data is redundant and repeated, and put them in a separate table. At this stage, you can already connect PHPMyAdmin or a similar utility and create tables directly on the server.
• If you overdid it, the system may work slowly, and you can do denormalization here and there . Load testing and experience will already help here.
• ???????
• PROFIT

If the subject area (your task) is simple, some steps can be done mentally.
This approach has been applied more than once live proved to be useful.

Answer 4

I think we need to start with the selection of entities and normalization. Proceeding from requests (well proekspleyniv) it is necessary to place indexes. If this is not enough, think about denormalization.

Answer 5

The picture is incomplete.
Where is the customer who certainly wants to introduce his smart thoughts into the structure of the tables / fields of the database?

Answer 6

Here is more on the topic eax.me/database-design

Answer 7

Thanks everyone)