Guide to designing relational databases (14-15 part of 15) [translation]
Continued.
Previous parts: 1-3 , 4-6 , 7-9 , 10-13
Continued. Cascade delete data.
You have met, I hope, with the basic concepts of creating databases and now you can design a simple relational database. In the example below, I will summarize the tasks you will encounter when developing a database.
PS The information below simulates the thinking process in a very simplified form when creating a database.
In order to get an idea of the data that will be used, let's define the tasks that the online store should perform.
')
From the list of tasks we can derive entities that have important roles in our system. Products , categories , customers and orders are entities that can be found in almost every online store database. In this example, I will show you a model containing only the following entities: customer , order and product . Having defined the entities, we can think about the connections between them.
This table is a simple example. The “real” customer table, of course, contains more information (address, city, etc.)
Some comments on this model.
Each order table entry, each order is associated with a unique customer record, with a unique customer using a foreign key - the customer_id field.
The number of orders.
If you have wondered if you can add, for example, the number of orders (order_quantity) field, then the answer is no. This data can be obtained from existing data . The total number of items in the order (order_quantity) can be obtained from the OrderProduct table. A query that finds the quantity of goods in an order can be easily generated using SQL.
Type of payment.
The field that you could add to the order table is payment_type (payment type). This information is unique to a specific order and cannot be obtained from other data (bear in mind that the payment_type field would become a foreign key in the order table — order — with a link to a separate table containing payment types).
The total amount of the order.
Another field that you can (and maybe should) add to the order table is the field for the total amount of the order. But you might think that we can get this data from the existing ones. You can add up the cost of all goods of the order? Yes. And no. The price of goods is a variable value. Therefore, when you calculate the total cost of the order, adding up the cost of each of its goods, and the store owner doubles the cost of one of the goods in the order, then the total cost of all orders already completed will also change. In other words, if you calculate the total cost of the order when viewing, and the prices of goods can change, then at the same time viewing the history, a situation may arise when the amount of money you paid for the entire order will change. That is why it is better to calculate the total cost at the time of placing the order and store it in the order table.
Storing the price history of the goods.
Speaking about the history, it can be assumed that you may need to save the price history for each product. In this case, you could look at the date of the order, make a request to the price_history table (price history) and get the cost of the goods on the date of the order. In this case, you would not have to store the total order value in the order table. I believe that most online stores retain the total value of the goods of the order and do not store price history for these goods. But, if you talk about you, the developer you and you decide to do it or not.
In the table of goods prices for goods are stored excluding VAT. The price with VAT can be calculated using program code or using an SQL query. That is why I do not keep prices with VAT included. You should know that storing the value of goods in this way can make sense in the future. In this model, the price of goods is stored in a single field of the table. Once you change the price of a product, you lose the previous value. But if you want to be able to receive past sales reports from your database, then you must keep a price history for each item. If a product has changed its value twice in a certain year, then you need a price history to know how much money you saved for that product in a given year. And since the VAT, the value of which increases the price of the product when it is sold, is not yours, it makes no sense to take it into account in reports on the profit received for the product.
Relational databases are an excellent tool for efficiently storing large amounts of information. In this guide, I focused primarily on building a database model. This model can be implemented using any RDBMS, and queries to it can be executed using SQL.
If you want to develop your database, be sure to get acquainted with the Mysql workbench . This is a great utility for creating entity-relationship diagrams and more. I use it widely in my work as a software developer, even if my work does not use the RDBMS Mysql.
Another logical step after reading this tutorial is to become familiar with the structured query language (SQL). Modeling databases using Mysql workbench or managing them using Sqlyog is all great, but ... if you really want to understand how to use databases, SQL is a skill without which you will not succeed. W3Schools has some good SQL lessons you can start with.
Previous parts: 1-3 , 4-6 , 7-9 , 10-13
Continued. Cascade delete data.
14. Another example: an online store database.
You have met, I hope, with the basic concepts of creating databases and now you can design a simple relational database. In the example below, I will summarize the tasks you will encounter when developing a database.
PS The information below simulates the thinking process in a very simplified form when creating a database.
The online store system.
In order to get an idea of the data that will be used, let's define the tasks that the online store should perform.
')
- Display of goods
- Product classification
- Customer registration
- Adding products to the shopping cart
- Display the contents of the shopping cart
- Making orders by visitors
- Etc.
We define the essence and relationships.
From the list of tasks we can derive entities that have important roles in our system. Products , categories , customers and orders are entities that can be found in almost every online store database. In this example, I will show you a model containing only the following entities: customer , order and product . Having defined the entities, we can think about the connections between them.
- There is a many-to-many relationship between an order and a product . Each order contains 1 or more products and each product may be associated with 0, 1 or more orders. A many-to-many relationship is created using three tables. Two tables - data sources (order - order and products - goods) and one - connecting (OrderProducts), which you can see in the picture below. Note that both orders and products have a one-to-many relationship to the join table. Together they form a many-to-many bond between orders and goods.
- Customers and orders have a one-to-many relationship. Each customer record can be associated with multiple order records (orders) and vice versa, each order record (a specific order) can be associated with only one customer record.
This table is a simple example. The “real” customer table, of course, contains more information (address, city, etc.)
Some comments on this model.
Order table (order)
Each order table entry, each order is associated with a unique customer record, with a unique customer using a foreign key - the customer_id field.
The number of orders.
If you have wondered if you can add, for example, the number of orders (order_quantity) field, then the answer is no. This data can be obtained from existing data . The total number of items in the order (order_quantity) can be obtained from the OrderProduct table. A query that finds the quantity of goods in an order can be easily generated using SQL.
Type of payment.
The field that you could add to the order table is payment_type (payment type). This information is unique to a specific order and cannot be obtained from other data (bear in mind that the payment_type field would become a foreign key in the order table — order — with a link to a separate table containing payment types).
The total amount of the order.
Another field that you can (and maybe should) add to the order table is the field for the total amount of the order. But you might think that we can get this data from the existing ones. You can add up the cost of all goods of the order? Yes. And no. The price of goods is a variable value. Therefore, when you calculate the total cost of the order, adding up the cost of each of its goods, and the store owner doubles the cost of one of the goods in the order, then the total cost of all orders already completed will also change. In other words, if you calculate the total cost of the order when viewing, and the prices of goods can change, then at the same time viewing the history, a situation may arise when the amount of money you paid for the entire order will change. That is why it is better to calculate the total cost at the time of placing the order and store it in the order table.
Storing the price history of the goods.
Speaking about the history, it can be assumed that you may need to save the price history for each product. In this case, you could look at the date of the order, make a request to the price_history table (price history) and get the cost of the goods on the date of the order. In this case, you would not have to store the total order value in the order table. I believe that most online stores retain the total value of the goods of the order and do not store price history for these goods. But, if you talk about you, the developer you and you decide to do it or not.
Product table.
In the table of goods prices for goods are stored excluding VAT. The price with VAT can be calculated using program code or using an SQL query. That is why I do not keep prices with VAT included. You should know that storing the value of goods in this way can make sense in the future. In this model, the price of goods is stored in a single field of the table. Once you change the price of a product, you lose the previous value. But if you want to be able to receive past sales reports from your database, then you must keep a price history for each item. If a product has changed its value twice in a certain year, then you need a price history to know how much money you saved for that product in a given year. And since the VAT, the value of which increases the price of the product when it is sold, is not yours, it makes no sense to take it into account in reports on the profit received for the product.
15. Conclusion and further reading.
Relational databases are an excellent tool for efficiently storing large amounts of information. In this guide, I focused primarily on building a database model. This model can be implemented using any RDBMS, and queries to it can be executed using SQL.
Where to go next?
If you want to develop your database, be sure to get acquainted with the Mysql workbench . This is a great utility for creating entity-relationship diagrams and more. I use it widely in my work as a software developer, even if my work does not use the RDBMS Mysql.
Another logical step after reading this tutorial is to become familiar with the structured query language (SQL). Modeling databases using Mysql workbench or managing them using Sqlyog is all great, but ... if you really want to understand how to use databases, SQL is a skill without which you will not succeed. W3Schools has some good SQL lessons you can start with.
Source: https://habr.com/ru/post/194714/
All Articles