Implementation of database model design relationships

In the object relationship model, we know there are three relationships: one-to-one, one-to-many, and many-to-many. This is only a conceptual relationship, but in a real relational database, we only have foreign keys, and there are no such three relationships, so let's talk about it in the relational database management system, how to implement these three relationships. One-to-multiple: here we will explain one-to-many, because

In the object relationship model, we know there are three relationships: one-to-one, one-to-many, and many-to-many. This is only a conceptual relationship, but in a real relational database, we only have foreign keys, and there are no such three relationships, so let's talk about it in the relational database management system, how to implement these three relationships. One-to-multiple: here we will explain one-to-many, because

In the object relationship model, we know there are three relationships: one-to-one, one-to-many, and many-to-many. This is only a conceptual relationship, but in a real relational database, we only have foreign keys, and there are no such three relationships, so let's talk about it in the relational database management system, how to implement these three relationships.

One-to-multiple

Here we will first explain one-to-many, because this relationship is the simplest. One-to-many and many-to-many are the same, so we will not mention the word "many-to-one. The one-to-multiple concept is that an object A corresponds to multiple objects B. From the perspective of B, an object B only corresponds to one object. For example, the relationship between a class and a student is one-to-many. One class corresponds to multiple students, and one student only applies to one class.

The reason why one-to-many relationships are simple is that the foreign keys of RDBMS actually represent one-to-many relationships. For a one-to-multiple relationship, we only need to establish a foreign key Association in the "multiple" table, while the table on the "one" side does not need to be modified. For example, the relationship between class and student. The class table remains unchanged. The class Id is added to the student table as the foreign key.

Many-to-many

Many-to-many relationships are more common in database design than one-to-one relationships. So here we will talk about many-to-many relationships. Many-to-many is that one object A corresponds to multiple objects B. From the perspective of B, one object B also corresponds to multiple objects. For example, the relationship between students and courses is many-to-many relationship. A student will take multiple courses and multiple students will take one course.

In RDBMS, an intermediate table must be used to represent many-to-many relationships. The intermediate table can be divided into two types: one is the intermediate table that purely represents the link, and the other is the intermediate table that represents the intermediate entity.

An intermediate table that purely represents A link is very simple. It only requires two columns: AID and BID. Keys other than AID are associated with the primary key of Table A, and keys other than BID are associated with the primary key of Table B, then these two columns form the joint primary key. This intermediate table is purely a multi-to-many relationship, and there is no corresponding entity in the business. For example, if we only need to know which students take the courses and which students choose the courses and do not need more information, we can create a "Student Course" Intermediate table, which contains only the student ID and course ID fields.

An intermediate object is a new entity formed by adding more attributes on the basis of a pure intermediate relational table. For example, if we need to record the time when a student chooses the course and the score after the student chooses the course, we are like building a "Course Selection" entity, the object has the following attributes:

• Course Selection ID, primary key
• Student ID, which is associated with the foreign key of the student table
• Course ID, which is associated with the foreign key of the curriculum
• Course Selection time, DateTime type
• Exam Score, which records the final score of the exam after the course is selected

This is an intermediate entity, which is completely out of the ordinary multi-to-Multi-link intermediate table and becomes an entity. Therefore, according to the primary key design principles mentioned in the previous blog, we can create a column of course ID as the primary key of the database, which has no business meaning.

One-to-one

In terms of concept, one-to-one means that an object A can correspond to at most one object B. From the perspective of B, it is also an object B that corresponds to at most one object. For example, the relationship between the class teacher (teacher) and the class. A class teacher can manage at most one class, and a class can have at most one class teacher.

One-to-one relationships are the least used in database design, because in general, if two entities are one-to-multiple relationships, we can also combine these two entities into one entity. However, in the design, we still encounter two completely different entities with a one-to-one relationship.

One-to-one RDBMS is implemented to create a foreign key pointing to another table on one of the tables, and to create a unique constraint on the foreign key column. For example, for the relationship between the class teacher and the class, we can create a class teacher field in the class table, and then create a unique constraint on this field. Because each class has a class teacher, the class teacher field cannot be blank. A teacher can be a class teacher in a certain class, or be an improper class teacher or a class teacher. At the same time, it is impossible to appear twice in the class teacher field of the class table. Therefore, a teacher can be a class teacher at most, therefore, this design meets the requirements.

Can we create the Managed class Id field in the instructor table, point to the class table, and create a unique constraint? No problem exists except for the business constraint that "each class must have a class teacher. Therefore, if one-to-one is required to hold the other side, the foreign key field is added to the other side. If there is no requirement to hold another type of entity, add any foreign key column.

Foreign keys and Indexes

Foreign keys are a constraint, which is different from the index concept. In most cases, when a foreign key is created, a corresponding index is created for the foreign key column. The existence of Foreign keys will check the constraints during each data insertion or modification. If the foreign key constraints are not met, data insertion or modification is prohibited, which will inevitably lead to a problem, that is, when the data volume is very large, each constraint check will inevitably lead to a decline in performance. In fact, there are similar problems with indexes. If more indexes are created, the corresponding indexes should be maintained during data insertion and deletion and modification. Therefore, the existence of indexes will also lead to slow data operations.

However, foreign keys and indexes have different advantages. Foreign keys only ensure data consistency and do not bring any benefits to system performance, therefore, the insertion speed of data due to foreign keys will become more and more serious as the data volume increases. The purpose of indexing is to retrieve data faster and to maintain index data changes, the impact on performance will not become as serious as the data volume increases as the foreign key does (of course, the index tree maintenance in the case of a large amount of data is more difficult than the index tree maintenance in the case of a small amount of data, but at least not like the foreign key ).

For the sake of performance, if our system is fully used by the program we developed, and we do not need to provide a database to other application systems to write data, and the performance requirements are high, therefore, we can consider not using foreign keys in the production environment. We only need to create indexes that can improve performance. Because the operations of the entire database are completed by the program we developed, we can check the consistency of all aspects during the development process to ensure that the operation data meets the foreign key constraints, without such a foreign key constraint. How can we achieve this? First, we have multiple scripts when creating a database, including creating tables, creating initialization data, creating indexes, and creating foreign keys, we have run these scripts in the development and testing environments to make the databases in the development and testing environments complete, after a large number of tests to ensure that the application can maintain the constraints between data, we do not need to run the create foreign key script file during production, you only need to create tables, initialize data, and create indexes.