Get best of the week

Relations entre les tables de base de données

1. Introduction


La communication est un sujet assez important qui doit être compris lors de la conception de bases de données. D'après ma propre expérience personnelle, je dirai que, après avoir réalisé les connexions, j'ai été beaucoup plus facile à comprendre la normalisation de la base de données.

1.1. A qui s'adresse cet article?


Cet article sera utile à ceux qui souhaitent comprendre les relations entre les tables de base de données. Dans ce document, j'ai essayé de dire en langage clair ce que c'est. Pour une meilleure compréhension du sujet, j'alterne le matériel théorique avec des exemples pratiques présentés sous la forme d'un diagramme et d'une requête qui crée les tableaux dont nous avons besoin. J'utilise le SGBD Microsoft SQL Server et j'écris des requêtes en T-SQL. Le code que j'ai écrit devrait fonctionner sur d'autres SGBD, car les requêtes sont universelles et n'utilisent pas de constructions T-SQL spécifiques.

1.2. Comment pouvez-vous appliquer ces connaissances?


  1. Le processus de création de bases de données deviendra plus facile et plus compréhensible pour vous.
  2. Comprendre les relations entre les tables vous aidera à mieux comprendre la normalisation, ce qui est très important lors de la conception d'une base de données.
  3. Traiter avec une base de données étrangère sera beaucoup plus facile.
  4. Lors de l'entretien, ce sera un très bon plus.

2. Remerciements


Les avis et critiques des auteurs ont été pris en compte. jobgemws, non rempli, sapin, Hamaruba.
Remercier!

3.1. Comment les communications sont-elles organisées?


Les liens sont créés à l'aide de clés étrangères.
Une clé étrangère est un attribut ou un ensemble d'attributs qui font référence à la clé primaire ou unique d'une autre table. En d'autres termes, c'est quelque chose comme un pointeur sur une ligne d'un autre tableau.

3.2. Types de relations


Les liens sont divisés en:

  1. Plusieurs à plusieurs.
  2. Un à plusieurs.
    • avec connexion obligatoire;
    • avec communication optionnelle;
  3. Un par un.
    • avec connexion obligatoire;
    • avec communication optionnelle;

Examinons en détail chacun d'eux.

4. Plusieurs à plusieurs


Imaginez que nous devons écrire une base de données qui sera stockée par un employé d'une entreprise informatique. Dans le même temps, il existe un certain ensemble standard de postes. Où:

  • Un employé peut avoir un ou plusieurs postes. Par exemple, un certain employé peut être à la fois administrateur et programmeur.
  • Un poste peut «posséder» un ou plusieurs employés. Par exemple, les administrateurs sont un ensemble spécifique de travailleurs. En d'autres termes, certains travailleurs appartiennent à des administrateurs.

Les employés sont représentés par la table des employés (id, nom, âge), les postes sont représentés par la table des postes (id et nom du poste). Comme vous pouvez le voir, ces deux tables sont connectées selon la règle plusieurs-à-plusieurs: chaque employé a un ou plusieurs postes (plusieurs postes), chaque poste a un ou plusieurs employés (plusieurs employés).

4.1. Comment construire de telles tables?


Nous avons déjà deux tableaux décrivant l'employé et la profession. Maintenant, nous devons établir de nombreuses relations entre eux. Pour mettre en œuvre une telle relation, nous avons besoin d'une sorte d'intermédiaire entre les tables «Employé» et «Poste». Dans notre cas, il s'agira d'un certain tableau «EmployésPositions» (travailleurs et postes). Cette table de médiation relie l'employé et le poste comme suit:
EmployeeIdPositionId
11
12
23
33
À gauche, les employés (leur identifiant), à droite, les postes (leur identifiant). Les employés et les postes sur ce tableau sont indiqués en utilisant id'shniki.

Ce tableau peut être vu de deux côtés:

  1. Ainsi, nous disons que l'employé avec id 1 est sur le poste avec id 1. Dans le même temps, faites attention au fait que dans ce tableau, l'employé avec id 1 a deux positions: 1 et 2. Autrement dit, chaque employé à gauche correspond à une certaine position à droite.
  2. Nous pouvons également dire que les messages avec l'ID 3 appartiennent aux utilisateurs avec l'ID 2 et 3. Autrement dit, chaque employé à droite a un certain employé à gauche.

4.2. la mise en oeuvre


Diagramme


Code T-SQL
create table dbo.Employee
(
	EmployeeId int primary key,
	EmployeeName nvarchar(128) not null,
	EmployeeAge int not null
)

--   Employee .
insert into dbo.Employee(EmployeeId, EmployeeName, EmployeeAge) values (1, N'John Smith', 22)
insert into dbo.Employee(EmployeeId, EmployeeName, EmployeeAge) values (2, N'Hilary White', 22)
insert into dbo.Employee(EmployeeId, EmployeeName, EmployeeAge) values (3, N'Emily Brown', 22)

create table dbo.Position
(
	PositionId int primary key,
	PositionName nvarchar(64) not null
)

--   Position .
insert into dbo.Position(PositionId, PositionName) values(1, N'IT-director')
insert into dbo.Position(PositionId, PositionName) values(2, N'Programmer')
insert into dbo.Position(PositionId, PositionName) values(3, N'Engineer')

--   EmployeesPositions .
create table dbo.EmployeesPositions
(
	PositionId int foreign key references dbo.Position(PositionId),
	EmployeeId int foreign key references dbo.Employee(EmployeeId),
	primary key(PositionId, EmployeeId)
)

insert into dbo.EmployeesPositions(EmployeeId, PositionId) values (1, 1)
insert into dbo.EmployeesPositions(EmployeeId, PositionId) values (1, 2)
insert into dbo.EmployeesPositions(EmployeeId, PositionId) values (2, 3)
insert into dbo.EmployeesPositions(EmployeeId, PositionId) values (3, 3)


Explication
foreign key primary key unique .

  • PositionId EmployeesPositions PositionId Position;
  • EmployeeId EmployeesPositions — EmployeeId Employee;


4.3. Conclusion


Pour mettre en œuvre des relations plusieurs-à-plusieurs, nous avons besoin d'un médiateur entre les deux tables considérées. Il doit stocker deux clés étrangères, dont la première fait référence à la première table et la seconde à la seconde.

5. Un à plusieurs


Il s'agit de la connexion la plus courante entre les bases de données. Nous le considérons après avoir lié plusieurs à plusieurs pour comparaison.

Supposons que nous devons implémenter une certaine base de données qui enregistre les données des utilisateurs. L'utilisateur a: nom, prénom, âge, numéros de téléphone. De plus, chaque utilisateur peut avoir un ou plusieurs numéros de téléphone (plusieurs numéros de téléphone).

Dans ce cas, nous observons ce qui suit: un utilisateur peut avoir plusieurs numéros de téléphone, mais on ne peut pas dire qu'un utilisateur spécifique appartient au numéro de téléphone.

En d'autres termes, le téléphone n'appartient qu'à un seul utilisateur. Un utilisateur peut posséder 1 ou plusieurs téléphones (plusieurs).

Comme nous pouvons le voir, cette relation est une à plusieurs.

5.1. Comment construire de telles tables?


Les utilisateurs seront représentés par un certain tableau «Personne» (identifiant, prénom, nom, âge), les numéros de téléphone seront représentés par le tableau «Téléphone». Il ressemblera à ceci:
PhoneIdPersonidNuméro de téléphone
1511 091-10
2519 124-66
31721 972-02
Ce tableau représente trois numéros de téléphone. Dans ce cas, les numéros de téléphone avec l'ID 1 et 2 appartiennent à l'utilisateur avec l'ID 5. Mais le numéro avec l'ID 3 appartient à l'utilisateur avec l'ID 17.
Remarque . Si la table «Téléphones» avait plus d'attributs, nous les ajouterions en toute sécurité à cette table.

5.2. Pourquoi ne faisons-nous pas ici une table intermédiaire?


Une table intermédiaire n'est nécessaire que si nous avons une relation plusieurs-à-plusieurs. Pour la simple raison que nous pouvons le considérer de deux côtés. Comme le tableau EmployeesPositions précédemment:

  1. Chaque employé a plusieurs postes (plusieurs).
  2. Chaque poste appartient à plusieurs employés (nombreux).

Mais dans notre cas, nous ne pouvons pas dire que chaque téléphone a plusieurs utilisateurs - un seul utilisateur peut appartenir à un numéro de téléphone.
Relisez maintenant la note à la fin du paragraphe 5.1. - cela deviendra plus compréhensible pour vous.

5.3. la mise en oeuvre


Diagramme


Code T-SQL
create table dbo.Person
(
	PersonId int primary key,
	FirstName nvarchar(64) not null,
	LastName nvarchar(64) not null,
	PersonAge int not null
)

insert into dbo.Person(PersonId, FirstName, LastName, PersonAge) values (5, N'John', N'Doe', 25)
insert into dbo.Person(PersonId, FirstName, LastName, PersonAge) values (17, N'Izabella', N'MacMillan', 19)

create table dbo.Phone
(
	PhoneId int primary key,
	PersonId int foreign key references dbo.Person(PersonId),
	PhoneNumber varchar(64) not null
)

insert into dbo.Phone(PhoneId, PersonId, PhoneNumber) values (1, 5, '11 091-10')
insert into dbo.Phone(PhoneId, PersonId, PhoneNumber) values (2, 5, '19 124-66')
insert into dbo.Phone(PhoneId, PersonId, PhoneNumber) values (3, 17, '21 972-02')


Explication
Phone . ( Person). , : « ». , id .

6. Un à un


Imaginez qu'au travail, vous avez été chargé d'écrire une base de données pour enregistrer tous les employés pour les RH. Le patron a assuré que l'entreprise ne devait connaître que le nom, l'âge et le numéro de téléphone de l'employé. Vous avez développé une telle base de données et y avez placé tous les 1000 employés de l'entreprise. Et puis le patron dit que pour une raison quelconque, ils doivent savoir si l'employé est handicapé ou non. La chose la plus simple qui vous vient à l'esprit est d'ajouter une nouvelle colonne booléenne à votre table. Mais il est trop long d'entrer 1 000 valeurs et vrai vous entrerez beaucoup moins souvent que faux (2% sera vrai, par exemple).

Une solution plus simple serait de créer une nouvelle table, appelons-la «DisabledEmployee». Il ressemblera à ceci:
DisabledPersonIdEmployeeId
1159
2722
3937
Mais ce n'est pas une relation un à un. Le fait est que dans un tel tableau, un employé peut être entré plus d'une fois; en conséquence, nous avons reçu une relation un-à-plusieurs: un employé peut être désactivé plusieurs fois. Il est nécessaire de s'assurer que l'employé ne peut être entré dans le tableau qu'une seule fois, respectivement, ne peut être désactivé qu'une seule fois. Pour ce faire, nous devons indiquer que la colonne EmployeeId ne peut stocker que des valeurs uniques. Nous avons juste besoin d'imposer une contrainte unique à la colonne EmloyeeId. Cette restriction signale qu'un attribut ne peut prendre que des valeurs uniques.

Ce faisant, nous avons eu une relation un à un.

La note. Notez que nous pourrions également imposer une contrainte de clé primaire à l'attribut EmloyeeId. Elle ne diffère de la contrainte unique que par le fait qu'elle ne peut pas être nulle.

6.1. Conclusion


On peut dire qu'une relation un-à-un est une division de la même table en deux.

6.2. la mise en oeuvre


Diagramme


Code T-SQL
create table dbo.Employee
(
	EmployeeId int primary key,
	EmployeeName nvarchar(128) not null,
	EmployeeAge int not null
)

insert into dbo.Employee(EmployeeId, EmployeeName, EmployeeAge) values (159, N'John Smith', 22)
insert into dbo.Employee(EmployeeId, EmployeeName, EmployeeAge) values (722, N'Hilary White', 29)
insert into dbo.Employee(EmployeeId, EmployeeName, EmployeeAge) values (937, N'Emily Brown', 19)
insert into dbo.Employee(EmployeeId, EmployeeName, EmployeeAge) values (100, N'Frederic Miller', 16)
insert into dbo.Employee(EmployeeId, EmployeeName, EmployeeAge) values (99, N'Henry Lorens', 20)
insert into dbo.Employee(EmployeeId, EmployeeName, EmployeeAge) values (189, N'Bob Red', 25)

create table dbo.DisabledEmployee
(
	DisabledPersonId int primary key,
	EmployeeId int unique foreign key references dbo.Employee(EmployeeId)
)

insert into dbo.DisabledEmployee(DisabledPersonId, EmployeeId) values (1, 159)
insert into dbo.DisabledEmployee(DisabledPersonId, EmployeeId) values (2, 722)
insert into dbo.DisabledEmployee(DisabledPersonId, EmployeeId) values (3, 937)


Explication
DisabledEmployee EmployeeId, . EmployeeId Employee. , unique, , . , .

7. Communications obligatoires et facultatives


Les relations peuvent être divisées en obligatoires et facultatives.

7.1. Un à plusieurs


  1. Un à plusieurs avec une connexion obligatoire: de
    nombreux soldats appartiennent à un même régiment. Un combattant appartient à un seul régiment. Veuillez noter que tout soldat appartient nécessairement à un régiment et qu'un régiment ne peut exister sans soldats.
  2. Un à plusieurs avec connexion en option:
    toutes les personnes vivent sur la planète Terre. Tout le monde ne vit que sur Terre. De plus, une planète peut exister sans l'humanité. En conséquence, nous trouver sur Terre est facultatif

La même relation peut être considérée comme obligatoire et facultative. Considérez cet exemple:
Une mère biologique peut avoir plusieurs enfants. Un enfant n'a qu'une seule mère biologique.
A) Une femme n'a pas nécessairement ses propres enfants. Par conséquent, la communication est facultative.
B) L'enfant n'a nécessairement qu'une seule mère biologique - dans ce cas, la communication est requise.

7.2. Un par un


  1. Un à un avec une connexion obligatoire:
    Un citoyen d'un certain pays ne doit avoir qu'un seul passeport de ce pays. Un passeport n'a qu'un seul titulaire.
  2. Un à un avec lien facultatif:
    un pays ne peut avoir qu'une seule constitution. Une constitution n'appartient qu'à un seul pays. Mais la constitution n'est pas contraignante. Un pays peut l'avoir ou non, comme, par exemple, Israël et la Grande-Bretagne.

La même connexion peut être considérée comme obligatoire et facultative:
Une personne ne peut avoir qu'un seul passeport. Un passeport n'a qu'un seul propriétaire.
A) La présence d'un passeport est facultative - un citoyen peut ne pas l'avoir. Ceci est un lien facultatif.
B) Un passeport ne doit avoir qu'un seul propriétaire. Dans ce cas, il s'agit déjà d'une connexion obligatoire.

7.3. Plusieurs à plusieurs


Toute relation plusieurs-à-plusieurs est facultative. Par exemple:
Une personne peut investir dans des actions de différentes sociétés (nombreuses). Les investisseurs de certaines entreprises sont certaines personnes (nombreuses).
A) Une personne ne peut pas du tout investir son argent dans des actions.
B) Personne ne pouvait acheter des actions de la société.

8. Comment lire les graphiques?


Ci-dessus, j'ai donné des diagrammes des tableaux que nous avons créés. Mais pour les comprendre, il faut savoir les "lire". Nous le comprendrons à l'aide de l'exemple du schéma du paragraphe 5.3.



Nous voyons une relation un-à-plusieurs. Une personne possède de nombreux téléphones.

  1. Près de la table Personne est une clé en or. Il désigne le mot «un».
  2. Près de la table du téléphone est un signe infini. Il désigne le mot «plusieurs».

9. Résumé


  1. Les connexions sont:
    • Plusieurs à plusieurs.
    • Un à plusieurs.
      1) avec communication obligatoire;
      2) avec un lien optionnel.
    • Un par un.
      1) avec communication obligatoire;
      2) avec un lien optionnel.
  2. Les connexions sont organisées à l'aide de clés étrangères.
  3. Une clé étrangère est un attribut ou un ensemble d'attributs qui font référence à la clé primaire ou unique d'une autre table. En d'autres termes, c'est quelque chose comme un pointeur sur une ligne d'un autre tableau.

10. Objectifs


Pour une meilleure assimilation du matériel, je vous propose de résoudre les problèmes suivants:

  1. : id, , , , . , , , .
  2. : id, , , . , .
  3. : , , ,
    • : id, , .
    • : id, .

    , — . , .
  4. 6.2. - , DisabledEmployee.

More articles:


All Articles