Optimisation base SQLite: index, foreign keys, constrains et triggers. Quoi utiliser et comment?
Salut à tous,
Je suis grand débutant pour ce qui est de monter une structure de base de données efficace. J'ai une idée bien précise de ce que je veux faire, et je voudrais avoir vos conseils pour savoir comment la mettre en œuvre. J'utilise une base SQLite.
J'ai 5 tables dans lesquelles je décompose un texte. De façon générale, la colonne ID est censée jouer le rôle classique d'index. Première question: est-ce que je devrais utiliser la fonction ROWID de Sqlite au lieu de cet index créé manuellement? Voilà une brève description des tables et leurs détails.
Table 1: paragraphes
Description: Table dans laquelle j'inscrit chaque paragraphe. J'y ajoute un ID qui doit être référencé dans la table "Sentence"
Détails:
Code:
1
2
3
4
5
6
7
8
9
10
11
| Table [Paragraph]
Fields: 2
[ID]: INTEGER NOT NULL ON CONFLICT REPLACE
[Paragraph]: TEXT
Foreign Keys: 0
Indexes: 1
[] PRIMARY ON CONFLICT REPLACE
[ID] COLLATE BINARYAUTOINCREMENT
Triggers: 0
Unique constraints: 0
Check constraints: 0 |
------------------------------------------------------------
Table 2: Sentence
Description: Table dans laquelle j'inscris chaque phrase. J'y ajoute un ID qui doit être référencé dans la table "text". Il faut, en plus, qu'il y ait un champ lié à la table 'Paragraph' , nommé 'relatedParagraphID' qui contient l'ID du paragraphe correspondant. Ce champ est rempli manuellement, mais j'aimerais que lorsque la valeur de référence (paragraphs.ID) change, il soit automatiquement mis à jour.
Détails:
Code:
1
2
3
4
5
6
7
8
9
10
11
12
| Table [Sentence]
Fields: 3
[ID]: INTEGER NOT NULL ON CONFLICT REPLACE
[Sentence]: TEXT
[RelatedParagraphID]: INTEGER NOT NULL ON CONFLICT IGNORE
Foreign Keys: 0
Indexes: 1
[] PRIMARY ON CONFLICT REPLACE
[ID] COLLATE BINARYAUTOINCREMENT
Triggers: 0
Unique constraints: 0
Check constraints: 0 |
------------------------------------------------------------
Table 3: Text
Description: Table dans laquelle j'inscris chaque mot dans l'ordre dans lequel il apparaît dans le texte. Il faut qu'il y ait un champ lié à la table 'Sentence' , nommé 'relatedSentenceID' qui contient l'ID de la phrase correspondante correspondant. De même que précédemment, ce champ est rempli manuellement, mais j'aimerais que lorsque la valeur de référence (Sentence.ID) change, il soit automatiquement mis à jour.
Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
| Table [Text]
Fields: 5
[ID]: INTEGER NOT NULL ON CONFLICT REPLACE
[Word]: CHAR(100)
[WordID]: INTEGER
[UniqueWordID]: INTEGER
[RelatedSentenceID]: INTEGER NOT NULL ON CONFLICT IGNORE
Foreign Keys: 0
Indexes: 2
[] PRIMARY ON CONFLICT REPLACE
[ID] AUTOINCREMENT
[TextIndex]
[Word] COLLATE BINARY
Triggers: 0
Unique constraints: 0
Check constraints: 0 |
------------------------------------------------------------
Table 3: Word
Description: Table dans laquelle j'inscris chaque mot utilisé, en regroupant les mots similaires. Le champ Word est supposé contenir des données uniques (pas deux fois le même contenu). Il faut qu'il y ait un champ lié à la table 'UniqueWord' , nommé 'relatedUniqueWordID' qui contient l'ID du mot unique correspondant. Je veux que ce champ se remplisse automatiquement à partir d'une condition " WHERE Word like UniqueWord.Flexions", et j'aimerais que lorsque la valeur de référence (UniqueWord.ID) change, il soit automatiquement mis à jour. Pour info, "Flexions" est une liste de string séparées par TAB.
Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
| Table [Word]
Fields: 2
[ID]: INTEGER NOT NULL ON CONFLICT REPLACE
[word]: CHAR(34) NOT NULL ON CONFLICT REPLACE
Foreign Keys: 0
Indexes: 2
[] PRIMARY ON CONFLICT REPLACE
[ID] COLLATE BINARYAUTOINCREMENT
[WordIndex]
[word] COLLATE BINARY
Triggers: 0
Unique constraints: 0
Check constraints: 0 |
----------------------------------------------------------------
Table 5: UniqueWords
Description: Table dans laquelle j'inscris chaque Lemme correspondant à mes mots dans le champ "Word" (si c'est un verbe conjugué, par exemple, le lemme, c'est son infinitif). J'y ajoute un ID qui doit être référencé dans la table "Word".
Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
| Table [UniqueWords]
Fields: 3
[ID]: INTEGER NOT NULL ON CONFLICT REPLACE
[word]: CHAR(34) NOT NULL
[Flexions]: TEXT
Foreign Keys: 0
Indexes: 2
[] PRIMARY
[ID] COLLATE BINARYAUTOINCREMENT
[UniqueWordIndex]
[word] COLLATE BINARY
Triggers: 0
Unique constraints: 0
Check constraints: 0 |
------------------------------------------------------------------
En synthèse, voilà mes questions:
- Je dois utiliser un ID manuel ou le ROWID Sqlite? Je veux juste qu'il soit autoincrémenté, constant, et unique.
- D'après mon type de données, considérez vous que la structure de la base convient?
- Je n'ai jamais utilisé d'index, mais apparemment ça améliore grandement les temps de requêtes. Est-ce que pour utiliser un index je suis censé lui donner un nom différent de celui de sa colonne ou si le nom est le même les données d'index seront automatiquement utilisées?
- C'est correct si je mets un index dans chaque colonne où je serai amené à faire une requête "WHERE"? Pas de souci si j'ai plusieurs index?
- Comment devrais-je réaliser les dépendances que je décris en détail de chaque table? (c'est la question la plus importante).
- Les types de données sont-ils corrects? Par exemple, pour un texte riche, dois-je préférer TEXT ou VARCHAR? Pour une valeur numérique entre 0 et 1 000 000, est-ce que INTEGER convient ou y a-t-il un type plus "économe"?
Une précision: pour une question de performance, la base SQLite n'est utilisée que pour sauvegarder des traitements. Le travail de traitement est effectué en mémoire (programme WPF en vb.net) après que j'ai rempli un dataset avec les données de la BDD SQLite.
par avance, merci pour vos réponses, qu'elles soient partielles ou incertaines, ça devrait m'aider à avancer.
