In anticipation of the start of the next stream at the rate prepared a small author's material with important tips on database design. We hope this material will be useful to you.
Databases are everywhere: from the simplest blogs and directories to reliable information systems and large social networks. It is not so important whether a database is simple or complex, how important it is to design it correctly. When the database is designed thoughtlessly and without a clear understanding of the purpose, it is not only ineffective, but further work with the database will be a real torment, an impenetrable forest for users. Here are some database design tips to help you create a useful and easy-to-use product.
1. Determine what the table is for and what its structure is
Today, development practices such as Scrum or RAD (Rapid Application Development) help IT teams develop databases quickly. However, in the pursuit of time, the temptation is very great to plunge immediately into building a base, vaguely imagining what the goal itself is, what the final results should be.
As if the team is aimed at efficient, high-speed work, but this is a mirage. The further and faster you dive deeper into the project, the more time it will take to identify and correct errors in the base project.
Therefore, the first thing to decide is to determine the purpose for your database. What type of application is the base being developed for? Will the user only work with records and need to pay attention to transactions, or is he more interested in data analytics? Where should the base be deployed? Will it track the behavior of customers or just manage the relationship between them?
The sooner the design team answers these questions, the smoother the database design process will be.
2. What data should I choose to store?
Plan ahead. Thoughts about what the site or system for which the database is being designed will do in the future. It is important to go beyond the simple requirements of the terms of reference. Just please don't start thinking about all the possible types of data a user will ever store. Instead, consider whether users will be able to post, upload documents or photos, or exchange messages. If so, then in the database you need to allocate space for them.
Work with the team, department, or organization that will support the design base in the future. Communicate with people of different levels, from account managers to heads of departments. So with the help of feedback, you will get a clear idea of ββthe requirements of the company.
Inevitably, the needs of users within even the same department will conflict. If you encounter this, don't be afraid to draw on your own experience and find a compromise that suits all parties and satisfies the ultimate goal of the database. Be sure: in the future you will receive +100500 in karma and a mountain of cookies.
3. Model data with care
There are a few key things to keep in mind when modeling data. As we said earlier, the purpose of the database determines which methods to use in modeling. If we are designing a database for online record processing (OLTP), in other words for creating, editing and deleting records, then we use transactional modeling. If the database must be relational, then it is best to use multidimensional modeling.
During modeling, conceptual (CDM), physical (PDM) and logical (LDM) data models are built.
Conceptual models describe entities and the data types they include, as well as the relationships between them. Divide your data into logical chunks - it's much easier to live that way.
The main thing is the measure, do not overdo it.
If an entity is very difficult to classify with a single word or phrase, then it's time to use subtypes (child entities).
If the entity leads its own life, has attributes that describe its behavior and its appearance, as well as relationships with other objects, then you can safely use not only a subtype, but also a supertype (parent entity).
If you ignore this rule, other developers will get confused in your model and will not fully understand the data and the rules for how to collect it.
Conceptual models are implemented with the help of logical ones. These models are like a roadmap for designing a physical database. In the logical model, business data entities are distinguished, data types are defined, the status of the key of the rule that governs the relationship between data.
Then the Logical data model is compared with the pre-selected DBMS (database management system) platform and the Physical model is obtained. It describes how data is physically stored.
4. Use the right data types
Using the wrong data type can result in less accurate data, difficulty joining tables, synchronizing attributes, and bloated file sizes.
To ensure the integrity of the information, an attribute should only contain data types that it accepts. If age is entered into the database, then make sure that the column stores integers with a maximum of 3 digits.
Create a minimum of null columns. If you create all columns as NULL, this is a blunder. If you need an empty column to perform a specific business function, when the data is unknown or does not yet make sense, then feel free to create it. After all, we cannot fill in the columns βDate of deathβ or βDate of dismissalβ in advance, we are not predictors pointing fingers at the sky :-).
Most modeling software (ER/Studio, MySQL Workbench, SQL DBM, gliffy.com) data allows you to prototype data regions. This ensures not only the correct data type, application logic, and good performance, but also the mandatory value specification.
5. Prefer natural
When deciding which column in a table to use as a key, always consider which fields the user can edit. Never choose them as a key - a bad idea. Anything can happen, and you must guarantee uniqueness.
It is best to use a natural, or business, key (natural key). It has semantic meaning, so you avoid duplication in the database.
If only the business key is not unique (first name, last name, position) and is repeated in different rows of the table or it must be changed, then the generated artificial, surrogate key (artificial key) should be assigned as the primary key.
6. Normalize in moderation
In order to effectively organize data in a database, a set of best practices must be followed and the database normalized. There are five normal forms to follow.
With normalization, you avoid redundancy and ensure the integrity of data used in an application or website.
As always, everything should be in moderation, even normalization. If there are too many tables in the database with the same unique keys, then you got carried away and over-normalized the database. Excessive normalization negatively impacts database performance.
7. Test early, test often
A test plan and proper testing should be part of the database design.
The best way to test a database is through Continuous Integration. Simulate the βA Day in the Life of a Databaseβ scenario and check if all edge cases are handled, what user interactions are likely. The sooner you find bugs, the more you save both time and money.
These are just seven tips to help you design a great performance and efficiency database. If you follow them, you will avoid most headaches in the future. These tips are just the tip of the iceberg in database modeling. There are a huge number of life hacks. Which ones do you use?
Source: habr.com