Basics of proper database design in web development
Databases are used everywhere, including most of the projects in the world of web development. Everything from the simplest blogs and directories to serious social web projects. Regardless of the complexity of the site and the corresponding database, each of them requires careful design to work efficiently and reliably.
In this article we will look at the basics of developing a good database plan, regardless of its final purpose. For all variants of database structure there is a set of standard rules and best practices that should be used. They will help keep the database organized and make it interact with the site in a more reasonable and efficient way.
The first method used in planning is brainstorming, making notes on paper or something else, depending on what is required to be stored in the database and what the site will need. Try not to think about specific fields, tables that will be used in a particular case - all specific points will be discussed by you later. Your goal at this stage is to get a general and complete picture of the database structure, which you will then refine and make more detailed. Often in the future it may be more difficult to add some elements to your plan than at the initial stage.
Move away from the database. Try to think what will be required of the site? For example, if you want to make a site that brings people together, you may immediately begin to think about the data that users will store. Forget it, save it for later. It is better to note that users and their information should be stored in a database. And what else? What will users do on your site? Will they post posts, upload files, photos, write messages to each other? Therefore, the database must store all this information: records, files, photos, messages, etc.
How will users interact with your site? Will they need to search, for example, their favorite recipes, have access to records available to a specific community, search for products or view a list of recently viewed and purchased products? The database should include the ability to store recipes, “closed” records, accessible to a specific circle of users, information about the products, as well as the possibility of communication between a specific product and the user.
The next step is to determine exactly which tables and fields will be required in the database. This is the core of the development and the most difficult part of it. Using the correct methods for linking tables, defining the data structure in each table, identifying the need for scattering these data across different tables — all these problems emerge from the direct design of the database. Now you need to define a list of obviously required tables and fields, be as specific as possible. During this process, some elements can be rebuilt or reorganized in order to improve the efficiency and security of the database.
')
Now that you know what the site will have to do, it's time to determine what specific information you need to store. A tool for database design will be very appropriate here, especially with the ability to create visual database models, for example, MySQL Workbench or DBDesigner4 . Gliffy is an excellent free online tool for creating various flowcharts and database models.
There is also a better known, quality, in my opinion, tool - Microsoft Visio (only under Windows, the price is $ 249.99). But do not worry, there are cheaper alternatives, many of which are open-source projects, including the two mentioned above.
Familiarize yourself with the general graphic symbols and standard visual elements required to create a database model, and begin pre-planning using flowcharts and diagrams. This will avoid logical errors before any specific database has already been created.
Most databases are relational databases . This means that the tables in the database are interconnected in some way. For example, if there is a “user” on the website of the Internet store, then it can certainly be associated with certain products, based on information about their orders or specifying the desired products. For a blog database, authors should be somehow related to the posts they wrote, and authorized users should be associated with the comments they left.
Using certain methods of relational databases, we can store a huge amount of information in an organized form in separate tables: one for users, one for records, one for comments, for products, etc. Now we can link the data in different tables using unique keys.
Any entry in each table must have a unique key. This type of "n about a measure of social insurance" or "bar code" to write. It is unique for each entry. And no other record can have the same identifier in the same table. Having unique names or product names in the database is not enough. It is much more efficient to use unique primary keys. Even a few unique fields in the database do not protect it from the possibility of data duplication, which can later adversely affect the operation of the site.
To link two tables, we use a foreign key, which is just an identifier that refers to a unique key in another table, usually the primary key. In the example below, we see that the first table contains information about three authors with a unique identifier (id). In the second table, we associate each entry about the article with the author through this identifier. Now we can find the author of the first article, and vice versa, see that Tom has written two articles, Mary - one, and Jane still not one.
This is a simple one-to-one relationship model. There are also one-to-many and many-to-many models.
As for the fields, it is also important to know when to group a certain part of the data, and when not. A good way to determine which information should be in one field or vice versa, to consider whether it will be necessary to change any part of it? For example, do you need to store the address, breaking it up into components: 1) street, 2) city, 3) state, 4) postal code, 5) country?
Is it an integral part of the site’s functionality (maybe users or administrators will want to search for other users by address or state), or just increase the space occupied by the database on disk? If this is not so important, then why load the database on changing 5 fields, when you can update only one string field. It may be more convenient to get this data from the HTML form, where the fields are separated, and before adding the address to the database, merge the values ​​from the corresponding fields into one line.
This is just one example, but always have an idea about the most effective ways of organizing table fields, when to combine them, when kept separately, in order to maintain the functionality of the site.
Normalization is a set of guidelines created to organize more efficient storage of information. We have already mentioned some important basic practices that are included in the most popular normal forms. There are five normal forms. It would be useful to familiarize yourself with these normal forms and develop databases in accordance with their requirements.
Database normalization is a big topic, but already understanding its basics can help you tremendously. To have a general idea of ​​each normal form and normalization as a whole, do not forget to take a look at the Basics of Normalizing Databases .
Database design is an extensive topic, but you will not need much to learn the basics and have an idea of ​​the correct database structure. Perhaps the most important stage in designing a database is its very beginning and brainstorming. This is what allows any developer to get all the necessary information in advance and implement his plans as needed. Only having all the necessary information for the design, you can create an effective database with correctly linked tables.
Any database must be efficient and scalable. Data is constantly being edited, added, deleted, so it will be important to keep the database organized in such a way as to maintain this constantly changing data set. Make sure that in the database being created only that information is deleted, which should not duplicate the records and could refer to other data easily and simply.
In this article we will look at the basics of developing a good database plan, regardless of its final purpose. For all variants of database structure there is a set of standard rules and best practices that should be used. They will help keep the database organized and make it interact with the site in a more reasonable and efficient way.
What functionality is required from the database
The first method used in planning is brainstorming, making notes on paper or something else, depending on what is required to be stored in the database and what the site will need. Try not to think about specific fields, tables that will be used in a particular case - all specific points will be discussed by you later. Your goal at this stage is to get a general and complete picture of the database structure, which you will then refine and make more detailed. Often in the future it may be more difficult to add some elements to your plan than at the initial stage.
Photo: binaryape
Move away from the database. Try to think what will be required of the site? For example, if you want to make a site that brings people together, you may immediately begin to think about the data that users will store. Forget it, save it for later. It is better to note that users and their information should be stored in a database. And what else? What will users do on your site? Will they post posts, upload files, photos, write messages to each other? Therefore, the database must store all this information: records, files, photos, messages, etc.
How will users interact with your site? Will they need to search, for example, their favorite recipes, have access to records available to a specific community, search for products or view a list of recently viewed and purchased products? The database should include the ability to store recipes, “closed” records, accessible to a specific circle of users, information about the products, as well as the possibility of communication between a specific product and the user.
Definition of required tables and fields
The next step is to determine exactly which tables and fields will be required in the database. This is the core of the development and the most difficult part of it. Using the correct methods for linking tables, defining the data structure in each table, identifying the need for scattering these data across different tables — all these problems emerge from the direct design of the database. Now you need to define a list of obviously required tables and fields, be as specific as possible. During this process, some elements can be rebuilt or reorganized in order to improve the efficiency and security of the database.
')
Use data modeling tool
Now that you know what the site will have to do, it's time to determine what specific information you need to store. A tool for database design will be very appropriate here, especially with the ability to create visual database models, for example, MySQL Workbench or DBDesigner4 . Gliffy is an excellent free online tool for creating various flowcharts and database models.
There is also a better known, quality, in my opinion, tool - Microsoft Visio (only under Windows, the price is $ 249.99). But do not worry, there are cheaper alternatives, many of which are open-source projects, including the two mentioned above.
Familiarize yourself with the general graphic symbols and standard visual elements required to create a database model, and begin pre-planning using flowcharts and diagrams. This will avoid logical errors before any specific database has already been created.
Relational databases
Most databases are relational databases . This means that the tables in the database are interconnected in some way. For example, if there is a “user” on the website of the Internet store, then it can certainly be associated with certain products, based on information about their orders or specifying the desired products. For a blog database, authors should be somehow related to the posts they wrote, and authorized users should be associated with the comments they left.
Using certain methods of relational databases, we can store a huge amount of information in an organized form in separate tables: one for users, one for records, one for comments, for products, etc. Now we can link the data in different tables using unique keys.
Any entry in each table must have a unique key. This type of "n about a measure of social insurance" or "bar code" to write. It is unique for each entry. And no other record can have the same identifier in the same table. Having unique names or product names in the database is not enough. It is much more efficient to use unique primary keys. Even a few unique fields in the database do not protect it from the possibility of data duplication, which can later adversely affect the operation of the site.
To link two tables, we use a foreign key, which is just an identifier that refers to a unique key in another table, usually the primary key. In the example below, we see that the first table contains information about three authors with a unique identifier (id). In the second table, we associate each entry about the article with the author through this identifier. Now we can find the author of the first article, and vice versa, see that Tom has written two articles, Mary - one, and Jane still not one.
This is a simple one-to-one relationship model. There are also one-to-many and many-to-many models.
Grouping and separating data
As for the fields, it is also important to know when to group a certain part of the data, and when not. A good way to determine which information should be in one field or vice versa, to consider whether it will be necessary to change any part of it? For example, do you need to store the address, breaking it up into components: 1) street, 2) city, 3) state, 4) postal code, 5) country?
Is it an integral part of the site’s functionality (maybe users or administrators will want to search for other users by address or state), or just increase the space occupied by the database on disk? If this is not so important, then why load the database on changing 5 fields, when you can update only one string field. It may be more convenient to get this data from the HTML form, where the fields are separated, and before adding the address to the database, merge the values ​​from the corresponding fields into one line.
This is just one example, but always have an idea about the most effective ways of organizing table fields, when to combine them, when kept separately, in order to maintain the functionality of the site.
Database normalization
Normalization is a set of guidelines created to organize more efficient storage of information. We have already mentioned some important basic practices that are included in the most popular normal forms. There are five normal forms. It would be useful to familiarize yourself with these normal forms and develop databases in accordance with their requirements.
Database normalization is a big topic, but already understanding its basics can help you tremendously. To have a general idea of ​​each normal form and normalization as a whole, do not forget to take a look at the Basics of Normalizing Databases .
Conclusion
Database design is an extensive topic, but you will not need much to learn the basics and have an idea of ​​the correct database structure. Perhaps the most important stage in designing a database is its very beginning and brainstorming. This is what allows any developer to get all the necessary information in advance and implement his plans as needed. Only having all the necessary information for the design, you can create an effective database with correctly linked tables.
Any database must be efficient and scalable. Data is constantly being edited, added, deleted, so it will be important to keep the database organized in such a way as to maintain this constantly changing data set. Make sure that in the database being created only that information is deleted, which should not duplicate the records and could refer to other data easily and simply.
Additional resources
- Database Design Guide
- 10 common mistakes in database design
- Introduction to Database Design
- Basics of Database Development
- 7 steps to proper database design
ps Claims for transfer to the PM are welcome. Thanks to everyone who did not interfere :)
Source: https://habr.com/ru/post/115777/
All Articles