How to competently develop a database?Asked by sinead
Always when developing a new project, the question arises how to competently develop a database in MySQL so that it would cope well with the workloads and would be convenient 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?
2. Define a set of fields for entities
3. After the entities are created, we normalize them - at least to the 3rd form it is necessary to bring
4. Again, look into those. the task and roughly throw in possible queries, select heavy queries (those that pull a lot of data, or which reload many tables) and try to remake the database structure so that the queries are simplified. Here is a stop: most often the main queries are SELECTs, but it happens that SELECT is made relatively rarely and fast INSERT data is much more important, you have to dance from here.
5. Again, we critically evaluate the structure, and again we throw approximate requests. Carefully look at INSERTs. We look, what INSERTs can be taken out in triggers. We throw an approximate body of the trigger, we trim the structure under it
6. Again, we critically evaluate the structure, we see what we lack. We are trying to make part of SELECTs in View, and part of stored procedures
7. Again, critically evaluate the resulting porridge. We are correcting something, we remember that we have missed something else. Go back to paragraph 3.
8. After it seems to us that everything has been done, we describe each table and field in words, describe in detail what and what we did, where we optimized and why.
9. The next morning we look again at our structure, we are terrified. Remember that yesterday made a description. Carefully read, we understand that we have missed something else. We are correcting, complementing.
10. We carry the structure to the next programmer and try to explain to him the flow of data. When it finally comes to him, ask what he could remake. He gives advice, after which you invent that "this was obvious." Again we redo everything.
11. When the neighbor programmer is tired of you and sent a fucking agreed with your structure, open the DBA, throw in the scheme, put down the keys. Again we understand that something is missing. We redesign the structure again.
12. Exhausted and angry go to bed. Morning is wiser than evening :)
and a general note - you need to determine for yourself: are you ready to scatter the application logic on the storage (DB) and on the code of the application itself. I personally decided for myself that I no longer want to work with a mixed structure - it is too hemorrhoid to support, and sometimes it is very difficult to track the changes that have been made.
but such a decision comes only after torment.
Good luck in the design! - jenny porter
Subject area. We describe (better not in the mind) simple Russian language - what we model. A database is always a model of something in reality. Enlarged basic objects and connections between them, without details. Tool - MS Word, notepad, Pens of the brain. Create a conceptual model. You can already draw pictures in the Word. Next comes the logical data model - it already has embryos of future tables, with the names of the fields and the connections between them. Here we indicate the type of relationships - 1: 1, 1: n, m: n. Here you can draw in a simple graphic editor or on the board. Next, the penultimate stage is the physical data model when specifying the field types. At this stage, it is less painful to use UML schemas, for example, using StarUML. And the last but cyclical stage is data normalization. Find out what data is redundant and repeat, 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 have overdone it, the system can work slowly, and you can do denormalization in some places. There will already help load testing and experience. ??????? PROFIT blockquote>
If the subject area (your task) is simple, some steps can be done in the mind.
This approach was used more than once to be useful.
1. Types of relationships are indicated already at the conceptual design stage.
2. There are no many-to-many connections at the stage of logical design.
3. Field types (did not understand that these are “field data types” or “attributes” are not important, as they are true for both cases) are indicated at the stage of logical design (moreover, as a first approximation, they can be considered and at the conceptual design stage).
4. Denormalization is far from the only way to speed up queries, first of all I would pay attention to indices, you can also optimize a query (for example, not to select a set according to the condition from the join results, but to combine the results of the condition).
I strongly support the idea of describing the subject area. And I recommend doing it not in the mind, but on paper. As long as the pattern in the mind is “simple and clear”, but as soon as you start to order your thoughts on paper, a lot of questions arise. - andi domeier
And the comprehension of the subject area is yes, a sore point of design.
At this stage, the expert in the automated process has to share knowledge with the programmer-designer. Sometimes information has to be pulled out with ticks and inhuman patience. - jonny illuminati
In general, I noticed that recently I stopped designing the database as such. Earlier, very often, a new application was started with this, and now I am designing a domain model (first with words, then diagrams, first of all with class diagrams), I implement these classes in the selected PL, and then “stupidly” duplicate (when almost automatically with pens) RDBMS class as a table, objects as strings, properties as fields, and forget about the database, until it comes (if it comes) the time to do optimization. - allie krause
Where is the customer who by all means wants to introduce his intelligent thoughts into the structure of the tables / fields of the database?
and the customer, who at the end of the work on TC says that the price is too high ...
etc. - mostafa
And there are programmers with basers pulling the blanket over themselves, where to implement logic, whether in classes, or in stored procedures. - stacye cotton