Shulou(Shulou.com)06/02 Report--

This article introduces the relevant knowledge of "what are the design rules of relational database". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

There are three main concepts in the Entity-relationship R (entity-relation) model: entity set, attribute and relation set.

An entity set (class) corresponds to a table in the database, and an entity (instance) corresponds to a row in the database table, also known as a record. An attribute corresponds to a column (column) in a database table, also known as a field.

ORM thought (Object Relational Mapping) embodies:

A class of database tables ↔ Java or Python

An object (or entity) in a ↔ class

A field, property (field) in a class ↔ in the table

Table relation design

There is a relationship between tables and data records between tables (relationship). All kinds of entities in the real world and the relationships between them are represented by relational models. There are four types of table relationships:

1. One to one association (one-to-one)

There are not many applications in actual development, because one-to-one can be created into a table.

For example: design student form: student number, name, mobile phone number, class, department, ID number, home address, place of origin, emergency contact, …

Split into two tables: the records of the two tables are one-to-one correspondence.

Basic information table (common information): student number, name, mobile phone number, class, department file information table (not commonly used information): student number, ID number, home address, place of origin, emergency contact, … There are two kinds of table-building principles: the foreign key is unique: the primary key of the primary table and the foreign key of the slave table (unique) form the primary foreign key relationship, and the foreign key is unique. The foreign key is the primary key: the primary key of the primary table and the primary key of the slave table, forming the primary and foreign key relationship.

2. One-to-many relationship (one-to-many)

Common example scenarios: customer table and order table, classification table and commodity table, department table and employee table.

For example: employee table: number, name, … , department table: number, name, brief introduction one-to-many table principle: create a field in the slave table (multi-party), and the field as a foreign key points to the primary key of the primary table (one party)

3. Many to many (many-to-many)

To represent a many-to-many relationship, you must create a third table, often referred to as a join table, which divides the many-to-many relationship into two one-to-many relationships. Insert the primary keys of both tables into the third table.

Example 1: student-course

Student information table: one line represents the information of a student (student number, name, mobile phone number, class, department … )

Course information table: one line represents the information of a course (course number, instructor, introduction … )

Course selection information table: a student can take multiple courses, and a course can be selected by multiple students.

Student number course No. 1 10012 10011 1002

Example 2: product-order

The order table and the products table have a many-to-many relationship, which is defined by establishing two one-to-many relationships with the order details table. An order can have multiple products, and each product can appear in multiple orders.

Product table: each record in the products table represents a product. Order table: each record in the orders table represents an order. Order schedule: each product can correspond to multiple records in the orders table, that is, it appears in multiple orders. An order can correspond to multiple records in the products table, that is, multiple products.

Example 3: user-role

Many-to-many relationship table building principle: you need to create a third table with at least two fields in the middle table, each of which acts as a foreign key pointing to the primary key of each party.

4. Self-citation (Self reference)

This is the end of the content of "what are the design rules of relational database". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.