Analyser la structure d’une table de base de données

  • 15 minutes

Comprendre comment les tables sont structurées et ce que vous pouvez utiliser dans Azure SQL est essentielle pour développer un bon modèle de données d’application.

Dans l’application de référence en ligne d’un jeu de cartes, de nombreux types de données différents doivent être stockés dans la base de données afin qu’ils puissent représenter les cartes physiques aussi précisément que possible dans ce support numérique.

Dans cette unité, vous allez découvrir les concepts de base des tables de base de données dans Azure SQL, la façon dont ces tables sont stockées ainsi que les types de données autorisées dans les tables.

Diagram of a database with pointers to separate tables showing Azure SQL holds tables.

Bases de données relationnelles et modèles de données

Une base de données relationnelle est un type de base de données où les données sont stockées dans des lignes et des colonnes qui forment ensemble des structures appelées tables. Elle s’apparente à une feuille de calcul Excel dans laquelle vous entrez des données. Vous pouvez associer des tables dans une base de données par des valeurs de clé uniques ; par exemple, un ID de produit dans une table de produits référencée dans une table de commandes. Vous pouvez également avoir des valeurs clés associées dans une hiérarchie avec une relation un-à-plusieurs ou parent-enfant. Un modèle de données est une représentation des tables d’une base de données relationnelle et de la façon dont ces tables sont liées ou se référencent les unes aux autres.

Diagram of a sample data model consisting of four tables.

Qu’est-ce qu’une table de base de données ?

Les tables sont l’un des nombreux objets d’une base de données relationnelle, mais sont de loin les plus importantes. Sans les tables, comment et où stockerions-nous les données ? Les tables sont similaires aux feuilles de calcul, composées de colonnes et de lignes. Pourquoi avoir une base de données au lieu de simplement utiliser des feuilles de calcul ? Imaginez que vous avez plusieurs milliers de feuilles de calcul stockées sur votre ordinateur, dont certaines contiennent des millions ou même des milliards de lignes. Comment allez-vous faire des recherches dans ces fichiers ? Comment les feuilles de calcul sont-elles logiquement et physiquement stockées et ordonnées ? Les bases de données nous permettent de stocker ces « feuilles de calcul » dans une méthodologie structurée et ordonnée, fournissant un accès instantané à n’importe quel enregistrement dans n’importe quelle table avec des vitesses de sous-seconde.

Comme les feuilles de calcul, les tables se composent de lignes (l’élément horizontal) et de colonnes (l’élément vertical). Vous pouvez considérer une colonne comme un attribut de ce que notre table stocke ou décrit. Une ligne d’une table peut être vue comme un enregistrement qui contient les données de ces colonnes. Lorsque des lignes et des colonnes se croisent, il s’agit d’un champ , comme une cellule d’une feuille de calcul. Bien que le nombre d’enregistrements, ou de lignes, vous pouvez stocker dans une table est limité par la quantité d’espace disque ou de stockage de l’ordinateur auquel votre base de données a accès, le nombre de colonnes est limité à 1 024. Avoir des tables avec des centaines de colonnes est difficile à conserver, comme la recherche d’un trombone dans le fond de votre tiroir indésirable. Les tables sont des structures permanentes dans une base de données, mais vous pouvez utiliser une table temporaire dans certains scénarios, que nous aborderons plus loin dans ce module.

Diagram of a sample table with multiple columns and rows.

Ces tables structurées en lignes et en colonnes stockent des données sous forme de nombres, de dates, de texte, d’objets binaires ou même de documents JSON. En plus des relations entre les tables, vous pouvez appliquer des ensembles de règles pour limiter ou enrichir les données pouvant être stockées dans la table.

Voici des exemples de règles applicables :

  • Possibilité de restreindre si un champ d’une ligne peut être null ou exiger une valeur
  • Affecter une valeur par défaut si aucune valeur n’est donnée
  • Définir et restreindre les valeurs acceptables
  • Insérer automatiquement des données dérivées d’une valeur calculée en fonction d’autres colonnes de la table

L’application d’informations de jeu de cartes aura des champs tels que le nom de la carte, l’ID de carte et la couleur de carte, chaque carte étant un enregistrement dans la table. Dans ce scénario, vous pouvez voir de quelle façon les lignes et les colonnes d’une table stockent des informations propres à l’une des cartes de jeu de l’image suivante.

Diagram showing how the columns of the cards table relates to the physical playing card of the scenario.

Tables optimisées en mémoire

Azure SQL dispose également de tables mémoire optimisées, de tables stockées dans la mémoire principale du serveur de base de données, où les lignes sont lues et écrites directement en mémoire. Toutefois, il existe également une copie physique de la table sur le disque pour une durabilité en cas de redémarrage ou de reprise d’activité. Pour vos besoins dans ce module, vous ne travaillerez pas avec des tables optimisées en mémoire, mais il est important de savoir qu’elles existent.

Comme vous le voyez, il est important de prendre des décisions intelligentes concernant la structure d’une table avant d’implémenter du code, car cela vous aidera à créer un modèle de données robuste et extensible qui pourra évoluer en même temps que votre application.

Comparer les types de données Azure SQL

Lorsque vous définissez les colonnes d’une table, non seulement devez-vous l’identifier avec un nom unique à la table, vous devez également l’affecter à un type de données. Les types de données sont des attributs qui spécifient le type des données que les colonnes peuvent contenir, par exemple, des nombres, du texte et des dates.

Définir les cas d’usage de chaque type de données

Avant toute chose, vous devez réfléchir au type des données que vous souhaitez stocker dans votre base de données. Est-ce que ce sont des valeurs monétaires ? Quelle est la taille prévue des nombres ? Avez-vous besoin de stocker des décimales ? L’application exige-t-elle que la base de données stocke des images ? Combien de texte une colonne doit-elle contenir ? Les réponses à ces questions déterminent les types de données utilisables pour chaque scénario.

Voici quelques-uns des types de données courants utilisés dans Azure SQL :

  • caractère: Lorsque vous souhaitez stocker des caractères ou du texte dans la base de données, Azure SQL fournit les types de données nchar et nvarchar . Utilisez nchar pour les données de texte de taille fixe et nvarchar pour les données de texte de taille variable. L’utilisation de max comme longueur de nvarchar permet de stocker du texte jusqu’à 2 Go de longueur par champ dans une ligne. Nchar et nvarchar autorisent également les caractères multioctets que vous voyez dans des langues telles que le japonais et le chinois.
  • Décimale : les nombres avec une précision spécifique utilisent le type de données decimal. Ce type de données doit être défini avec deux variables. La première est la précision (p), ou le nombre total maximal de chiffres décimaux à stocker. Deuxièmement, la ou les échelles, le nombre de chiffres décimaux stockés à droite de la virgule décimale.
  • entiers: Lorsque vous stockez des nombres exacts qui n’ont pas besoin de porter des valeurs décimales, vous pouvez utiliser integer types. La plupart des cas d’usage utilisent le type de données int , mais il existe d’autres types entiers pour des cas spéciaux. Pour les petites valeurs, vous pouvez utiliser tinyint et smallint; pour les grands nombres, bigint est préférable. Le type de données money s’utilise pour stocker des montants en devise.
  • Bit : Le type de données bit ne peut contenir qu’un 0 ou un 1, ce qui le rend parfait pour les données booléennes ou true/false.
  • Date et heure : comme pour les types de données numériques, vous pouvez stocker des dates et l’heure dans la base de données avec différents niveaux de précision. Le type de données date stocke les données dans la base de données au format AAAA-MM-DD. Si vous avez besoin d’une plus grande précision, vous pouvez utiliser le type de données datatime2 , qui stocke la date au format AAAA-MM-DD hh :mm:ss[.nnnnnnnnn]. Si vous avez juste besoin du temps, vous pouvez utiliser le type de données time , qui utilise le format hh:mm:ss[.nnnnnnn]. Si vous créez une application à l’esprit avec la mondialisation, vous pouvez utiliser le type de données datetimeoffset, qui contient des informations de fuseau horaire.
  • Binaire : Si vous devez stocker des données telles que des images ou des fichiers, vous pouvez utiliser les types de données binary et varbinary . Le type de données binary concerne les données binaires de longueur fixe, tandis que varbinary concerne les données binaires de longueur variable.
  • Spatial: Azure SQL a deux types de données spatiales ; geometry et geography. Le type geometry représente les données d’un système de coordonnées euclide (plat), tandis que le type geography représente les données dans un système de coordonnées autour de la terre. Une fois que les données sont stockées dans la base de données à l’aide de ces types, vous pouvez effectuer des opérations spatiales avec SQL, telles que les requêtes voisines les plus proches (où se trouve le restaurant de pizza le plus proche de mon emplacement) ou les emplacements de points dans un espace géométrique (où x, y et z se croisent sur un graphique).

Avec votre nouvelle connaissance des types de données de base de données, reportez-vous au modèle de données de l’application de jeu de cartes. Vous pouvez voir que les colonnes telles que card_id et card_power seront des types de nombres, où card_name, card_typeet card_color seront des types de données texte. L’art de carte doit être stocké. Par conséquent, l’utilisation de varbinary à sa taille maximale vous permet de stocker des images dans la table.

Comment les tables sont-elles stockées ?

Sur le système de fichiers où réside la base de données, les tables sont stockées dans des pages. Ces pages sont des fichiers de 8 Ko qui sont fournis sous forme de pages de données, de texte/image ou d’index. Comme l’index d’un livre ou d’un article, chaque page commence par une section d’en-tête qui décrit ce qui se trouve dans la page. Ces informations d’en-tête sont notamment le numéro de page, le type de page, la quantité d’espace disponible sur la page et l’ID d’unité d’allocation de l’objet propriétaire de la page. Les étendues contiennent huit pages contiguës physiquement, par conséquent, les bases de données Azure SQL ont 16 étendues par mégaoctet. (64 Ko par étendue * 16 étendues = 1 024 Ko ou 1 Mo) Pour en savoir plus à ce sujet, consultez le Guide d’architecture des pages et des étendues.

Diagram showing eight pages to an extent for storing SQL Server tables.

Pourquoi la façon dont les tables sont stockées est-elle importante ? Lorsque vous concevez le modèle de données et choisissez les types de données et leurs tailles à utiliser, gardez à l’esprit que les données en trop dans une page ne peuvent pas continuer dans une autre page. La création de colonnes avec des types de données qui utilisent toujours la taille maximale peut entraîner des problèmes de performances, car les pages potentiellement peuvent être créées pour chaque ligne.

Vérifiez vos connaissances

1.

Une application à visée mondiale nécessite que les heures soient stockées avec les informations de fuseau horaire. Quel type de données serait approprié ?

2.

Quelle taille ont les fichiers de pages qui stockent les données de table ?