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?


1. Select all the necessary entities (objects)
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 :)

© me
remark to 3. it is possible and to boys-kodda

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
In my opinion, you need to put logic in the database no earlier than this application-side logic becomes a bottleneck. You can estimate in advance what you can put in the database and what a win it will give, but you should not hurry, while the reaction time is acceptable, really the support turns into hell. And even when it is unacceptable, and there are no other optimization options, it’s still to think again, and whether it’s better to replace the server and / or expand the cluster :) - jennifer arnold
benjamin reeves
The material on this topic is link even within the runet. But first, it is advisable to read about normalization and functional dependencies. Examples of good design can be found in open source projects, for example, in bagzilla.
isaac bridges
We were taught in link like this:
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
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.
Beg not agree:
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
p.1 - thanks, you completed the work for me, I just forgot to decipher the term “conceptual model” in my comments. Otherwise, you allowed yourself to disagree completely over trifles.
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
Techniques two - normalization with the initial design, denormalization after load testing (and sometimes after the first launch of the query :)).

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
oops, did not click - nicole greaves
I think we should start with the selection of entities and normalization. Based on the queries (properly projected), you should place the indexes. If this is not enough - think about denormalization.
adam lunde
Picture is incomplete.
Where is the customer who by all means wants to introduce his intelligent thoughts into the structure of the tables / fields of the database?
well, then add another person responsible for the project in a large company that runs to the director and coordinates everything with him ...
and the customer, who at the end of the work on TC says that the price is too high ...
etc. - mostafa
Well, from whom it hurts, he says something about that.
And there are programmers with basers pulling the blanket over themselves, where to implement logic, whether in classes, or in stored procedures. - stacye cotton
I just didn’t find a normal way to keep track of versioning changes, especially when they don’t allow refactoring ((( - rehab
yes, the customer is familiar with the customer ... especially if the customer thinks he is a genius and makes adjustments that make the project more difficult. - jodi sh
Here are more on the topic
Thank you all)
Where can I find a remote program for $ 500 per month? :: What system of control over work and bugs to choose? :: In the context menu of Windows Explorer 7, items of the foobar2000 player appear and disappear? :: Serif fonts and without? :: From which site you can take gasoline prices for the project?
Leave Repply for How to competently develop a database?
Useful Links