Foreign key in the e-commerce website database design use not much ah?

Foreign key in the e-commerce website database design use not much ah?

For example, used in some related tables, feel very practical, to ensure that there is no wrong data rows, and useless data rows, but also some people say try not to use foreign keys, in the program to control the integrity of the data constraints can be, otherwise inconvenient maintenance, I do not know exactly how good.

Reply content:

Foreign key in the e-commerce website database design use not much ah?

For example, used in some related tables, feel very practical, to ensure that there is no wrong data rows, and useless data rows, but also some people say try not to use foreign keys, in the program to control the integrity of the data constraints can be, otherwise inconvenient maintenance, I do not know exactly how good.

But some people say try not to use foreign keys, in the program to control the integrity of data constraints can be, otherwise inconvenient maintenance
You want to see who said it.
Many programmers do not have the same database level as users. These people think that the procedure is omnipotent.
Many DBAs think the data is the most important, longer than the program. The program is not available, and the data remains an important asset for the enterprise.

Without a foreign key, the integrity of the data is not guaranteed. If you think this is acceptable, of course you can not use foreign keys.
Foreign keys are basic database constraints, and if this is omitted, then your database is not called a database at all. Many people say that relational database performance is poor, in fact, most of them are poorly designed.

Let's talk about the disadvantages of the foreign keys that many people call.
Foreign keys can cause inconvenience

1. For example, deleting a data error
   I don't think it's a bad thing, especially for users. Think of programming, the compiler will also error, we know that this is not a bad thing, but in advance to prevent errors.

2. Also, for example, BULK INSERT
   If you can be sure that the data is OK, the DBMS provides the option to ignore the foreign key constraint. However, when the input source of the data is unreliable, data inconsistency is easy, so most of the time foreign keys (and other constraints) are necessary.

Foreign keys require additional overhead to reduce performance
Any code has overhead, and the key is not worth it. Do nothing, is the fastest, do not need to spend time.
Because the correctness of the data is crucial, it is certainly worthwhile to use foreign keys.

Even without the foreign key, you have to control the correctness of the data in the program. So this overhead is necessary and cannot be dispensed with.
There are many drawbacks to program control over data integrity, such as
1 program Bug
Basically there is no bug-free program, which means that the foreign key function cannot be reliably replaced by the program.

2 strong coupling of data and programs
The database can only be used by one program, to support multiple programs, and to ensure data integrity,
A) either repeat the logic
It is obviously bad that each program controls the consistency of the data itself.
b) Access to the database either through a common interface
This is a more popular way. 3-tier architecture, SOA, ...
I dare not say that these architectures are all wrong and they all have specific uses. But you have to understand that the more complex a system, the more parts, the greater the likelihood of error, and the worse the performance.

All in all, if you think the correctness of the data must be guaranteed, then you have to pay a certain price to achieve. It is more reliable to use the foreign key than the program control, and it is simpler and more straightforward, which relieves the burden of the program.
DBMS has a history of more than 40 years, is a top-notch programmer developed by the C + +, after a lot of testing, by countless projects, its reliability and performance is close to the optimal state.
If you think the programmers in your project can do a better job, have a lot of familiarity with the technology of business and concurrency, and have plenty of time, then you can use program control.

To be honest, many of the projects I touch are not constrained by foreign keys, and many of them do not consider standardized design. Such a system is complex (no need to be so complicated) and performance is not good. This is my own firsthand.

Of course, all things can not be generalize, without the foreign key program can do very well, sell very well. When you make a decision, be clear about the consequences.
I personally prefer the classic approach to reliable methods.

Foreign keys ensure data integrity and consistency, for example:
A commodity order form, an Order Detail table, an order ID for the Order Details table, is dependent on the ID of the order form.
Student tables, student scores, school numbers in the score table depend on the primary key in the student table.

In other words, the order ID in the Order Details table can be inserted in the order form, and the academic number in the score table must be present to insert it.

The trouble is that when you delete a student, you delete the data that has dependencies,

And the MySQL MyISAM does not support foreign keys, [setting is also invalid]

Look at the situation and preferences. Some foreign keys can be released, it is more flexible, put in the program to maintain.

