How many foreign keys are used in e-commerce website database design?

How many foreign keys are used in e-commerce website database design? For example, when used in some associated tables, it is very practical to ensure that no erroneous data rows and useless data rows are generated. However, some people say that foreign keys should not be used as far as possible, you can control the integrity of data in the program, otherwise it will not... how many foreign keys are used in e-commerce website database design?

For example, when used in some associated tables, it is very practical to ensure that no erroneous data rows and useless data rows are generated. However, some people say that foreign keys should not be used as far as possible, you can control the integrity of data in the program. Otherwise, maintenance is not convenient and I don't know how it works.

Reply content:

How many foreign keys are used in e-commerce website database design?

For example, when used in some associated tables, it is very practical to ensure that no erroneous data rows and useless data rows are generated. However, some people say that foreign keys should not be used as far as possible, you can control the integrity of data in the program. Otherwise, maintenance is not convenient and I don't know how it works.

HoweverSomeoneTry not to use foreign keys, and control the integrity of data in the program. Otherwise, maintenance is not convenient.
You need to see who said it.
The database level of many programmers is inferior to that of users. These people think that the program is omnipotent.
Many DBAs think that data is the most important and longer than programs. The program is useless, and data is still an important asset of the enterprise.

Data integrity cannot be guaranteed without foreign keys. If you think this is acceptable, you do not need a foreign key.
Foreign keys are basic database constraints. If they are omitted, your database cannot be called a database. Many people say that relational databases have poor performance. In fact, most of them are poorly designed.

Next we will talk about the disadvantages of Foreign keys that many people have mentioned.
Foreign keys will cause inconvenience

1. For example, an error occurred while deleting a data record.
   I think this is not a bad thing, especially for users. When thinking about programming, the compiler also reports errors. We all know that this is not a bad thing, but it prevents errors in advance.

2. For example, batch insert
   If you are sure that the data is correct, DBMS provides the option to ignore the foreign key constraint. However, when the data input source is unreliable, data inconsistency may easily occur. Therefore, foreign keys (and other constraints) are necessary most of the time.

Foreign keys require additional overhead to reduce performance
Any code has overhead. The key is not worth it. Nothing is done. It is the fastest and does not take time.
Because data correctness is crucial, it is worth using foreign keys.

Even if you do not need a foreign key, you must control the correctness of the data in the program. Therefore, this overhead is required and cannot be saved.
Data Integrity Control through programs has many disadvantages, such
1 program bug
Basically, there is no bug-free program. That is to say, the foreign key function is not available.ReliableReplaced by programs.

2. Strong coupling between data and programs
A database can only be used by one program. It must support multiple programs and ensure data integrity,
A) either repeat the logic
The consistency of data controlled by each program is obviously bad.
B) either access the database through a common interface
This is a comparisonPopular. Layer-3 architecture, SOA ,...
I dare not say that these architectures are all wrong and they all have specific purposes. But you need to understand that the more complex a system is, the more parts it has, the more likely it is to make an error and the worse its performance.

All in all, if you believe that data correctness must be ensured, you must pay a certain price for it. The use of foreign keys is more reliable than the use of program control, and is simpler and more direct, reducing the burden on the program.
DBMS has a history of 40 years. It was developed by top programmers using C/C ++. After many tests, it has been used by countless projects, and its reliability and performance are close to the optimal state.
If you think that programmers in your project can do better, are very familiar with transactions, concurrency and other technologies, and have enough time, you can use program control.

To be honest, many of my projects do not require foreign key constraints, and many do not consider standardized design. Such a system is complex (not necessary) and has poor performance. This is my personal experience.

Of course, all things cannot be generalized. Programs that do not use foreign keys can do well and sell well. When you make a decision, you need to understand the consequences.
I personally prefer the classic and reliable methods.

The foreign key can ensure data integrity and consistency. For example:
The Order id of a product order table, Order Details table, and Order Details table depends on the order table id,
The student ID in the student table, student renewal table, and student renewal table depend on the primary key in the student table.

In other words, the Order id in the Order Details table must exist in the order table before it can be inserted. The student id in the orders table must exist in this student id before it can be inserted.

The trouble is that when deleting a student, you must delete the dependent data accordingly,

Mysql myisam does not support foreign keys. [The settings are also invalid]

View the situation and preferences. Some foreign keys can be opened, which is more flexible and maintained in the program.