Foreign key of the primary key of the Master/Slave table

The primary key of Table A acts as a foreign key in Table B. Who is the primary table and who is the subtable?

A as the primary table
B is the slave table

I personally think:

A foreign key is a type of constraint. There is no master-slave relationship, and only the reference relationship exists, such as the Department table and employee table.
Each employee belongs to a department and must correspond to the information of a department.
The relationship between the master and slave tables is similar to that between the order table and the order list.
Hope the above is helpful.

The C field in Table A contains the primary key and multiple values in Table B. How can I design a database?
Create Table B
(
C char (6) primary key,
C_name varchar (50) not null
)
Go
Create Table
(
A_id char (6) primary key,
A_name varchar (100) not null,
C char (6) References B (c) -- set the foreign key for field C in Table
)
Go

Must the foreign key of the slave table and the column name of the primary key of the master table be the same? What is the value?
The column names do not have to be the same, but the values of Foreign keys are either retrieved from the primary key domain or null, which is called the reference integrity rule in the database.

 

The primary key and foreign key are the adhesives that organize multiple tables into a valid relational database. The primary key and foreign key design have a decisive impact on the performance and availability of the physical database.

The database mode must be converted from the logical design to the physical design. The structure of the primary key and the foreign key is the crux of this design process. Once the database is used in the production environment, it is difficult to modify these keys. Therefore, it is necessary and worthwhile to design the primary keys and Foreign keys during the development stage.

Primary key:

Relational databases depend on primary keys. It is the cornerstone of the physical database mode. The primary key has only two purposes on the physical layer:

1. uniquely identifies a row.

2. As an object that can be effectively referenced by a foreign key.

Based on the above two purposes, the following provides some principles that I follow when designing the primary keys of the physical layer:

1. Primary keys should be meaningless to users. If you see data in a connection table that represents many-to-many relationships and complain about its usefulness, it proves that its primary key is well designed.

2. Primary keys should be single columns to improve the efficiency of connection and filtering operations.

Note: people who use composite keys usually have two reasons for self-release, which are both incorrect. The first is that the primary key should have practical significance. However, making the primary key meaningful only makes it easy to artificially damage the database. The second is that two external keys can be used as the primary key in the connection table describing multiple-to-multiple relationships. I also oppose this approach because: composite primary keys often lead to bad Foreign keys. That is, when the connected table becomes another master table of the slave table, it becomes part of the primary key of the table according to the second method above. However, this table may become another master table of another slave table, and its primary key may be a part of another primary key of the slave table. If this is passed on, the closer it is to the back of the slave table, the primary key will contain more columns.

3. Never update the primary key. In fact, because the primary key only identifies a row and has no other purposes, there is no reason to update it. If the primary key needs to be updated, it indicates that the primary key should be meaningless to the user.

Note: This principle is not applicable to data that often needs to be sorted during data conversion or multi-database merger.

4. The primary key should not contain dynamically changed data, such as the timestamp, Creation Time column, and modification time column.

5. The primary key should be automatically generated by a computer. If a person intervene in the creation of a primary key, it will have a meaning other than a unique row. Once this boundary is crossed, the motive for modifying the primary key may be generated, this system is used to link record rows and manage record rows. It will fall into the hands of people who do not know the database design.