As we approach the launch of the next course session, we have prepared a brief original piece with important tips on database construction. We hope you find this material useful.
Databases are everywhere: from simple blogs and directories to reliable information systems and large social networks. It is not as important whether the database is simple or complex; what matters is designing it correctly. When a database is designed thoughtlessly and without a clear understanding of its purpose, it is not only ineffective, but also makes further work with the database a real ordeal, a dense forest for users. Here are some tips on database construction that will help create a useful and user-friendly product.
1. Define the purpose of the table and its structure
Today, development methods like Scrum or RAD (Rapid Application Development) help IT teams to develop databases quickly. However, in the rush for time, there is a great temptation to dive directly into building the database, vaguely understanding what the goal is and what the end results should be.
It's as if the team aims for effective, fast work, but that is an illusion. The deeper and faster you dive into the project, the more time it will take to identify and correct errors in the database project.
Therefore, the first thing to decide is to define the purpose for your database. What type of application is the database being developed for? Will the user only be interacting with records and should attention be paid to transactions, or is the focus more on data analytics? Where should the database be deployed? Will it track customer behavior or simply manage relationships?
The sooner the design team answers these questions, the smoother and easier the database design process will be.
2. What data to choose for storage?
Plan ahead. Think about what the website or system for which the database is being designed will do in the future. It's important to go beyond simple requirements in the technical specifications. But please, don’t start thinking about all possible types of data that users will ever store right away. Instead, consider whether users will be able to write posts, upload documents or photos, or exchange messages. If so, you need to allocate space for them in the database.
Work with the team, department, or organization that will support the designed database in the future. Communicate with people at different levels, from customer service specialists to department heads. This way, through feedback, you will gain a clear understanding of the company's requirements.
Inevitably, user needs within even one department will conflict. If you encounter this, don’t hesitate to rely on your own experience to find a compromise that satisfies all parties and meets the ultimate goal of the database. Rest assured, in the future, you'll earn +100500 karma and a mountain of cookies.
3. Model data cautiously
There are several key points to consider when modeling data. As we mentioned earlier, the purpose of the database determines which methods to use in modeling. If we are designing a database for the operational processing of records (OLTP), that is, for creating, editing, and deleting them, then we use transaction modeling. However, if the database needs to be relational, it's best to apply multidimensional modeling.
During modeling, conceptual (CDM), physical (PDM), and logical (LDM) data models are built.
Conceptual models describe entities and the types of data they encompass, as well as the relationships between them. Break your data into logical chunks — it’s much easier to manage.
The main thing is balance; don’t overdo it.
If an entity is very difficult to classify with a single word or phrase, it's time to use subtypes (child entities).
If an entity leads its own life, has attributes that describe its behavior and appearance, as well as relationships with other objects, it is safe to use not only the subtype but also the supertype (parent entity).
If you ignore this rule, other developers will get confused with your model and will not fully understand the data and the rules on how to collect them.
Conceptual models are implemented using logical models. These models act like a roadmap for designing the physical database. In the logical model, business data entities are singled out, data types are defined, and key status rules that regulate relationships between data are established.
Then the logical data model is mapped to the selected database management system (DBMS) Database Management System) resulting in a physical model. It describes the method of physically storing the data.
4. Use appropriate data types
Using an incorrect data type can lead to less accurate data, difficulties in merging tables, synchronizing attributes, and bloating file sizes.
To ensure information integrity, an attribute must contain only acceptable data types for it. If age is entered into the database, make sure that the column holds integer values of a maximum of 3 digits.
Create a minimum of empty columns with NULL value. If you create all columns as NULL, it is a serious mistake. However, if you need an empty column to fulfill 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 pre-fill columns like 'Date of Death' or 'Date of Termination'; we are not fortune tellers pointing fingers in the air :-).
Most modeling software (ER/Studio, MySQL Workbench, SQL DBM, gliffy.com) allows for prototyping data areas. This ensures not only the correct data type, application logic, and good performance, but also the mandatory assignment of values.
5. Prefer natural
When deciding which column in the table to choose as a key, always pay attention to which fields the user can edit. Never select them as a key — it's a bad idea. Anything can happen, and you must guarantee uniqueness.
It's best to use a natural or business key. It has semantic value, so you avoid duplication in the database.
Unless the business key is not unique (like first name, last name, title) and repeats in different rows of the table or needs to change, you should assign a generated artificial surrogate key as the primary key.
6. Normalize in moderation
To effectively organize data in a database, you need to follow a set of recommendations and normalize the database. There are five normal forms that need to be followed.
With normalization, you will avoid redundancy and ensure data integrity used in the 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, you've overdone it and excessively 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.
It's best to test the database using Continuous Integration. Model a scenario 'A day in the life of a database' and check if all boundary cases are handled, what user interactions are likely. The earlier you find bugs, the more time and money you will save.
These are just seven tips that can help you design a great database for performance and efficiency. 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 countless hacks. Which ones do you use?
Source: habr.com
