The Basics of Proper Database Design in Web Development
- Transfer
Databases are used everywhere, including most of the projects in the world of web development. Everything from simple 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 in order to work efficiently as well as reliably.
In this article, we will cover the basics of developing a good database plan, regardless of its final purpose. For all variants of the database structure, there is a set of standard rules and best practices that should be used. They will help the database stay organized and make its interaction with the site in a more reasonable and effective way.
The first method used in planning is a regular brainstorming session, making notes on paper or something else, depending on what needs to be stored in the database and what the site will require. Try not to think about specific fields, tables that will be used in a particular case - all specific points will be considered 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.
Get out of the database. Try to think what will be required from the site? For example, if you want to create a site that brings people together, you may immediately start thinking about the data that users will store. Forget put it off for later. Better write down that users and information about them should be stored in a database. And what else? What will users do on your site? Will they publish records, 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, for example, their favorite recipes, have access to records available to a particular community, search for products or view a list of recently viewed and purchased products? The database should provide for the ability to store recipes, “closed” records available to a certain circle of users, product information, as well as the possibility of communication between a specific product and user.
The next step is to determine which tables and fields are required in the database. This is the core of the development and its most difficult part. Using the correct methods for linking tables, determining the data structure in each table, identifying the need for scattering this data across different tables — all these problems come up with the direct design of the database. Now you need to define a list of obviously needed tables and fields, be as specific as possible. During this process, some elements can be rearranged or reorganized in order to increase 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 will need to be stored. A tool for designing databases will be very appropriate here, especially having 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, high-quality, in my opinion, tool - Microsoft Visio (only under Windows, price $ 249.99). But don't be alarmed, there are cheaper alternatives, many of which are open-source projects, including the two mentioned above.
Familiarize yourself with the common graphic symbols and standard visual elements needed to create a database model, and begin your preliminary planning with flowcharts and diagrams. This will avoid logical errors before any specific database is 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 an online store, then it can certainly be associated with certain products, based on information about their orders or indicating the desired products. For a blog database, authors must be somehow connected to the posts they wrote, and authorized users must be linked to 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 notes, one for comments, for products, etc. Now we can bind data in different tables using unique keys.
Any entry in each table must have a unique key. It’s like “n osocial insurance measure ”or“ barcode ”for the record. It is unique to each entry. And no other record can have the same identifier in the same table. Having unique or product names in the database is not enough. Much more efficient is the use of unique primary keys. Even several unique fields in the database do not protect it from the possibility of data duplication, which subsequently can adversely affect the site.
To connect the two tables, we use a foreign key, which is just an identifier that refers to a unique key in another table, usually it is a 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 article entry with the author through this identifier. Now we can find the author of the first article, and vice versa, see that Tom wrote two articles, Mary - one, and Jane not one more.
This is a simple one-to-one relationship model. There are also one-to-many and many-to-many models .
As for fields, it is also important to know when to group a certain piece of data, and when not. A good way to determine what information should be in one field or vice versa, to think about whether it will be necessary to change any part of it? For example, is it necessary to store the address, breaking it down into its components: 1) street, 2) city, 3) state, 4) postal code, 5) country?
Is it an integral part of the site’s functionality (perhaps users or administrators will want to search for other users by address or state), or simply 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 just one string field. A more convenient option would be to get this data from an HTML form, where the fields are separated, and before adding the address to the database, combine the values from the corresponding fields into one line.
This is just one example, but always have an idea of the most effective ways to organize table fields, when to combine them, when to keep them separate, in order to maintain the functionality of the site.
Normalization is a set of guidelines designed to organize more efficient storage of information. We have already mentioned some important basic practices that come in the most popular normal forms. There are five normal forms. It would be useful to familiarize yourself with these normal forms and design databases in accordance with their requirements.
Normalizing a database is a big topic, but understanding its basics can be extremely helpful. In order to have a general idea of each normal form and normalization in general, do not forget to take a look at the Fundamentals of Database Normalization .
Database design is an extensive topic, but you don’t need to do much to learn the basics and have an idea of the correct database structure. Perhaps the most important step 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 the plan as necessary. Only having all the necessary information for design, you can create an effective database with properly linked tables.
Any database must be efficient and scalable. Data is constantly edited, added, deleted, therefore, it will be important to keep the database organized in such a way as to support this constantly changing data set. Make sure that only the information that should be deleted in the created database is not duplicated, and that other data could be referenced easily and simply.
In this article, we will cover the basics of developing a good database plan, regardless of its final purpose. For all variants of the database structure, there is a set of standard rules and best practices that should be used. They will help the database stay organized and make its interaction with the site in a more reasonable and effective way.
What functionality is required from the database
The first method used in planning is a regular brainstorming session, making notes on paper or something else, depending on what needs to be stored in the database and what the site will require. Try not to think about specific fields, tables that will be used in a particular case - all specific points will be considered 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
Get out of the database. Try to think what will be required from the site? For example, if you want to create a site that brings people together, you may immediately start thinking about the data that users will store. Forget put it off for later. Better write down that users and information about them should be stored in a database. And what else? What will users do on your site? Will they publish records, 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, for example, their favorite recipes, have access to records available to a particular community, search for products or view a list of recently viewed and purchased products? The database should provide for the ability to store recipes, “closed” records available to a certain circle of users, product information, as well as the possibility of communication between a specific product and user.
Defining required tables and fields
The next step is to determine which tables and fields are required in the database. This is the core of the development and its most difficult part. Using the correct methods for linking tables, determining the data structure in each table, identifying the need for scattering this data across different tables — all these problems come up with the direct design of the database. Now you need to define a list of obviously needed tables and fields, be as specific as possible. During this process, some elements can be rearranged or reorganized in order to increase the efficiency and security of the database.
Use a data modeling tool
Now that you know what the site will have to do, it's time to determine what specific information will need to be stored. A tool for designing databases will be very appropriate here, especially having 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, high-quality, in my opinion, tool - Microsoft Visio (only under Windows, price $ 249.99). But don't be alarmed, there are cheaper alternatives, many of which are open-source projects, including the two mentioned above.
Familiarize yourself with the common graphic symbols and standard visual elements needed to create a database model, and begin your preliminary planning with flowcharts and diagrams. This will avoid logical errors before any specific database is 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 an online store, then it can certainly be associated with certain products, based on information about their orders or indicating the desired products. For a blog database, authors must be somehow connected to the posts they wrote, and authorized users must be linked to 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 notes, one for comments, for products, etc. Now we can bind data in different tables using unique keys.
Any entry in each table must have a unique key. It’s like “n osocial insurance measure ”or“ barcode ”for the record. It is unique to each entry. And no other record can have the same identifier in the same table. Having unique or product names in the database is not enough. Much more efficient is the use of unique primary keys. Even several unique fields in the database do not protect it from the possibility of data duplication, which subsequently can adversely affect the site.
To connect the two tables, we use a foreign key, which is just an identifier that refers to a unique key in another table, usually it is a 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 article entry with the author through this identifier. Now we can find the author of the first article, and vice versa, see that Tom wrote two articles, Mary - one, and Jane not one more.
This is a simple one-to-one relationship model. There are also one-to-many and many-to-many models .
Grouping and sharing data
As for fields, it is also important to know when to group a certain piece of data, and when not. A good way to determine what information should be in one field or vice versa, to think about whether it will be necessary to change any part of it? For example, is it necessary to store the address, breaking it down into its components: 1) street, 2) city, 3) state, 4) postal code, 5) country?
Is it an integral part of the site’s functionality (perhaps users or administrators will want to search for other users by address or state), or simply 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 just one string field. A more convenient option would be to get this data from an HTML form, where the fields are separated, and before adding the address to the database, combine the values from the corresponding fields into one line.
This is just one example, but always have an idea of the most effective ways to organize table fields, when to combine them, when to keep them separate, in order to maintain the functionality of the site.
Database normalization
Normalization is a set of guidelines designed to organize more efficient storage of information. We have already mentioned some important basic practices that come in the most popular normal forms. There are five normal forms. It would be useful to familiarize yourself with these normal forms and design databases in accordance with their requirements.
Normalizing a database is a big topic, but understanding its basics can be extremely helpful. In order to have a general idea of each normal form and normalization in general, do not forget to take a look at the Fundamentals of Database Normalization .
Conclusion
Database design is an extensive topic, but you don’t need to do much to learn the basics and have an idea of the correct database structure. Perhaps the most important step 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 the plan as necessary. Only having all the necessary information for design, you can create an effective database with properly linked tables.
Any database must be efficient and scalable. Data is constantly edited, added, deleted, therefore, it will be important to keep the database organized in such a way as to support this constantly changing data set. Make sure that only the information that should be deleted in the created database is not duplicated, and that other data could be referenced easily and simply.
Additional resources
- Database Design Guide
- 10 common mistakes in database design
- Introduction to Database Design
- Database Design Basics
- 7 steps to proper database design