[MS SQL SERVER]conception du DB pour application multilingue

Version imprimable

09/06/2011, 17h03
Kropernic

[MS SQL SERVER]conception du DB pour application multilingue

Bonjour à tous,

Tout d'abord, désolé si je ne suis pas dans le bon forum. J'ai cherché un forum conception mais je ne l'ai pas trouvé.

J'aurai souhaité avoir quelques conseils quant à la conception d'une DB devant gérer des infos en plusieurs langue (2 dans mon cas).

Je me rends bien compte que le système que j'ai mis en place actuellement n'est franchement pas top et qu'il faut que je décompose en plusieurs requête ce qui, je pense, aurait pu être fait en une seule si la DB avait été bien conceptualisée.

Pour le moment, j'ai quelque chose du genre (les clés primaires sont en gras souligné) :

tb_client :
- id : int
- nom : varchar(100)
- langage : smallint
- param1 : int
- param2 : int
...
- paramn : int

tb_langage :
- id : int
- description_fr : varchar(20)
- description_nl : varchar(20)

tb_param1 :
- id : int
- description_fr : varchar(100)
- description_nl : varchar(100)

Je ne vais pas plus loin, je pense que vous avez compris l'idée.

Du coup, si je veux imprimer un quelconque rapport (genre une commande par exemple) dans la langue du client, je dois d'abord faire une requête afin d'obtenir la langue du client pour que dans l'application je puisse "créer" la requête en prenant le champ de description correspondant à la langue.

En tant que programmeur, je me rend bien compte qu'il y a un problème car si un jour une 3e langue vient s'ajouter, mon application ne sera pas du tout fonctionnelle pour cette dernière. Je cherche donc à modifier la DB (tant pis si ça me prend pas mal de temps à tout refaire) de façon à ce que l'application n'ait pas besoin de traiter la langue.

J'ai songé à faire qqch du genre :

tb_paramx :
- id : int
- id_langue : int
- description : varchar(100)

Mais cela multiplierait le nombre de record de chaque table de paramètre par le nombre de langue utilisée. Et n'ayant aucune "vraie" formation en DB (j'ai lu des trucs par-ci par-là), je m'interroge sur les bienfaits de ce genre de pratique.

Je suis donc à la recherche d'avis de gens compétents en la matière pour me conseiller sur l'approche à adopter pour traiter ce genre de cas.

Merci d'avance.

Griftou.
09/06/2011, 19h12
elsuket

Bonjour,

Citation:

En tant que programmeur, je me rend bien compte qu'il y a un problème car si un jour une 3e langue vient s'ajouter, mon application ne sera pas du tout fonctionnelle pour cette dernière. Je cherche donc à modifier la DB (tant pis si ça me prend pas mal de temps à tout refaire) de façon à ce que l'application n'ait pas besoin de traiter la langue.

Pourtant là vous avez l'air de bien vous être rendu compte que c'est un problème, et je ne peux que saluer votre démarche.

Je n'ai pas saisi le contexte complet de votre environnement.
L'idée c'est de créer une table de langues, et une table des traductions.

Supposons que le nom de vos clients peut être stocké en Thaï (exemple au hasard ... ou pas :)), et dans une transcription du Thaï vers l'alphabet latin.

Vous aurez donc :

- une entité client
- une entité langue
- une entité client_nom_traduction

Les entités client et langue restent à peu près identiques à ce ce que vous avez pour le moment.
L'entité client_nom_traduction référence l'entité client et l'entité langue.
Pour tout tuple de cette entité, il existe une et une seule traduction.

Évidemment c'est un peu lourd si vous avez un nombre conséquent d'entités, mais cela a le mérite d'être flexible et robuste.

Pour exposer vos données, je ne sais pas si vous utilisez des procédures stockées, mais il vous suffit alors de passer un paramètre qui définit la langue dans laquelle l'interrogation ou la modification de données doit avoir lieu.
Mais visiblement, c'est déjà ce que vous faites.

Le forum que vous cherchiez est celui-ci.
Si vous réalisez votre implémentation sous SQL Server, nous serons néanmoins ravis de vous aider ;)

@++ ;)

Je vous recommande de ne pas faire de table pour chaque champs à traduire mais plutôt pour l'ensemble des éléments sensibles à la langue pour chacune de vos table existante.

Vous devriez d'abord rajouter une table table_langue dans votre DB.
Et ensuite pour chaque table table_x une table table_trad_de_x lié à une ligne de table_x et une langue de la table table_langue.

Ensuite faite une vue pour chaque table originale.
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 CREATE VIEW .... SELECT T.id , T.c1 , T.c2 , ISNULL(TRAD.c3, T.c3) AS c3 -- colonne sensible à la langue , ISNULL(TRAD.c4, T.c4) AS c4 -- colonne sensible à la langue FROM table_x AS T CROSS JOIN table_langue AS L LEFT JOIN table_trad_de_x AS TRAD ON ( TRAD.X_ID = T.id AND TRAD.LANG_ID = L.ID )
Et ensuite pour être vraiment peinard, créer un trigger instead of pour l'insertion, la suppression et si besoin, l'update) de la vue pour pouvoir tout faire directement sur la vue depuis vos programmes.

10/06/2011, 14h41
iberserk

Citation:

Et ensuite pour chaque table table_x une table table_trad_de_x lié à une ligne de table_x et une langue de la table table_langue.

Citation:

Et ensuite pour être vraiment peinard, créer un trigger instead of pour l'insertion, la suppression et si besoin, l'update) de la vue pour pouvoir tout faire directement sur la vue depuis vos programmes.

Pensez aussi à un généreux TRIGGER DML sur vos table 'originales' pour appliquer les mêmes modifications sur la structure de vos tables 'tablelangue' :-)
14/06/2011, 09h39
Kropernic

Bonjour à tous,

Tout d'abord, merci pour vos réponses.

Par contre, peut-être que mon w-e prolongé y est pour quelque chose mais je ne suis pas sûr de bien comprendre tout ce que vous me proposez. D'autant plus que vous semblez vous contredire l'un l'autre ^^.

Je vais donc tenter de préciser le contexte de mon problème.

J'ai une table qui contient des informations sur des personnes dont voici une partie de la structure (la totalité serait trop longue :aie:) (l'application est destinée à avoir une gestion du personnel interne à l'entreprise plutôt que de passer par un secrétariat social dès qu'on a besoin d'une info)

tb_personnel :
- registre_national : char(11) : primary key
- matricul : char(7)
- date_entree : datetime
- date_sortie : datetime
- nom : varchar(50)
- prenom : varchar(50)
- adresse : varchar(100)
- caractere_juridique_contrat : smallint
- categorie_salariale : smallint
- etat_civil : smallint
- langue : smallint
- fonction : smallint
- formation : smallint

Je pense que cela suffira pour mon problème.

J'ai donc chaque membre du personnel représenté dans cette table.
A côté de cela, j'ai une table langue qui se compose comme suit :

tb_langue :
- id : int : primary key (auto inc.)
- description_fr : varchar(100)
- description_nl : varchar(100)

Et en fait, j'ai utilisé la même structure que la table langue pour toutes les autres données qui varient suivant la langue (toutes les colonnes de types smallint dans la table personnel).

Exemple--> tb_caractere_juridique_contrat :
- id : int : primary key (auto inc.)
- description_fr : varchar(100)
- description_nl : varchar(100)

Déjà, si un jour on veut ajouter une langue, c'est la catastrophe (mais cela a très peu de chance d'arriver).

Maintenant, par rapport à vos réponses, ce que je n'ai pas compris c'est s'il me fallait des tables supplémentaires ou bien s'il "suffisait" que je modifie leur structure actuelle.

N'étant absolument pas formé en tant que DBA, j'aurai probablement beaucoup de mal à mettre en place la solution vraiment optimale que, j'imagine, vous ne manquerez pas de me proposer. Je recherche juste une solution conceptualisation de la DB qui soit plus flexible et qui ne demande pas de changer de requête dès qu'on change de langue.

Au risque de me répéter, j'ai songé à modifier la structure des tables de clés étrangères comme suit (exemple avec caractere_juridique_contrat) :

tb_caractere_juridique_contrat :
- id : smallint
- id_langue : smallint
- description : varchar(100)

Si je ne me trompe pas, cela résoudrait le problème de requête qu'il faut modifier pour chaque langue. Par contre cela va créer énormément plus de record que la version actuelle de la DB et j'ignore si cela peut poser des problèmes ou non (autres que des problèmes d'espace disque).

Concernant vos propositions de vues, de triggers et de procédures stockées, je crains fort de ne pas être compétent pour mettre cela en place à l'heure actuelle (mais des formations sont fort heureusement prévues). Histoire que vous vous rendiez compte de l'état de mes connaissances, mes programmes accèdent tous sur les DB avec le login "sa" et je gère les autorisations au sein de l'application en elle-même car il s'agit là de mon domaine d'expertise.

Voilà, vous pouvez jeter les pierres:aie:

Griftou.
14/06/2011, 11h05
elsuket

Citation:

Maintenant, par rapport à vos réponses, ce que je n'ai pas compris c'est s'il me fallait des tables supplémentaires ou bien s'il "suffisait" que je modifie leur structure actuelle.

...

Au risque de me répéter, j'ai songé à modifier la structure des tables de clés étrangères comme suit (exemple avec caractere_juridique_contrat) :

tb_caractere_juridique_contrat :
- id : smallint
- id_langue : smallint
- description : varchar(100)

Si je ne me trompe pas, cela résoudrait le problème de requête qu'il faut modifier pour chaque langue. Par contre cela va créer énormément plus de record que la version actuelle de la DB et j'ignore si cela peut poser des problèmes ou non (autres que des problèmes d'espace disque).

La table tb_caractere_juridique_contrat telle que vous la proposez est correcte.

Il vous faudra, comme je vous l'ai décrit plus haut, ajouter une table pour chaque traduction à réaliser, et c'est ce que vous avez fait avec la table tb_caractere_juridique_contrat.

Les requêtes seront en conséquence écrites une seule fois, et vous pourrez ajouter autant de langues que vous voulez.

Il est vrai que cela va créer dans les tables de traduction beaucoup de lignes, mais comme votre table a peu de colonnes, les pages qui stockent les données seront très riches.
Sous réserve d'une indexation correcte et de requêtes propres, le nombre de lignes des tables de traduction ne devrait donc pas poser problème.

Citation:

Concernant vos propositions de vues, de triggers et de procédures stockées, je crains fort de ne pas être compétent pour mettre cela en place à l'heure actuelle

- Une procédure stockée est un module T-SQL qui peut prendre en entrée des paramètres, et retourner des valeurs ou ensembles de valeurs (ou rien)
Elle est composée d'instruction T-SQL, paramétrées ou non.

- Une vue est, en SQL, une table particulière, qui est une représentation purement logique (sauf si elle est indexée, mais c'est un cas particulier d'optimisation) d'un ensemble
Elle est spécifiée par une requête SELECT et ne peut prendre aucun paramètre.
On la manipule comme une table (on l'interroge comme telle, on peut y attacher des triggers, ...)

- un trigger est un type particulier de module SQL qui est attaché à une table (ou à une base de données, ou à une instance SQL Server), qui ne peut prendre aucun paramètre.
Il exécute un traitement après (AFTER) ou au lieu (INSTEAD OF) qu'un certain type de transaction ait été effectué sur une table et c'est vous qui le choisissez (INSERT/UPDATE/DELETE).
Il permet (mais ce n'est pas obligatoire) d'effectuer des traitements à partir des données manipulées par la transaction qui a déclenché le trigger.

L'utilisation de triggers dans votre cas ne me semble pas nécessaire.
Les vues le seront peut-être, les procédures probablement, mais rien ne vous empêche de faire tout cela côté applicatif.
L'avantage des procédures stockées, c'est que côté applicatif, vous ne faites que l'appeler en lui passant des paramètres.

@++ ;)
14/06/2011, 11h14
Kropernic

Merci bien !

Je vais donc m'atteler à modifier tout cela.

Griftou.
14/06/2011, 12h09
Kropernic

Rebonjour,

Je reviens vers vous car j'ai un nouveau souci suite à la modification des tables de ce que j'appelle les paramètres pour gérer les langues.

Une fois cela fait, j'ai évidemment voulu recréer également les liaisons entre les différentes tables. Et c'est là que ça coince. Forcément, puisque dans ma table caractere_juridique_contrat (pour continuer avec le même exemple) se trouve repris le même id pour chaque langue (2 fois donc dans mon cas). De ce fait, la contrainte d'unicité pour établir la relation de clé étrangère (toute mes excuses si j'utilise mal le vocabulaire) n'est pas respectée et la relation ne peut se faire.

Je conçois donc bien que je dois également tenir compte de la langue mais je ne comprends pas comment cela fonctionne en pratique...

Lorsque sur le diagramme je veux faire la liaison, Management Studio me propose bien :

1 : pour la table de clé primaire :
- caractere_juridique_contrat
- langue

2 : pour la table de clé étrangère :
- caractere_juridique_contrat

Je pourrais cliquer sur ok et passer à la suite mais je préfèrerais comprendre ce qu'il se passe exactement.

Pouvez-vous m'éclairer sur ce point ? (en fait c'est tellement flou que je ne suis même pas certain d'avoir correctement formulé ma demande...)

Griftou.
14/06/2011, 13h32
elsuket

Donnez s'il vous plaît le script complet de la table caractere_juridique_contrat avec les colonnes qui doivent être traduites.
De cette façon, ce sera plus concret.

@++ ;)

Je crois qu'il y a un malentendu du au fait que je ne dois probablement pas utiliser le bon vocabulaire ^^.

Quoi qu'il en soit, voici ce que me donne managament studio pour le script de création de table de caractere_juridique_contrat:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
/****** Object:  Table [HumanRessources].[caractere_juridique_contrat]    Script Date: 06/14/2011 13:39:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [HumanRessources].[caractere_juridique_contrat](
	[caractere_juridique_contrat] [smallint] NOT NULL,
	[langue] [smallint] NOT NULL,
	[description] [varchar](100) COLLATE Latin1_General_CI_AS NOT NULL,
 CONSTRAINT [PK_caractere_juridique_contrat_langue] PRIMARY KEY CLUSTERED 
(
	[caractere_juridique_contrat] ASC,
	[langue] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
 
GO
SET ANSI_PADDING OFF

Voici également la même chose pour la table personnel :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
/****** Object:  Table [HumanRessources].[personnel]    Script Date: 06/14/2011 13:41:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [HumanRessources].[personnel](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[registre_national] [char](11) COLLATE Latin1_General_CI_AS NULL,
	[date_entree] [datetime] NULL,
	[suspension_debut] [datetime] NULL,
	[suspension_fin] [datetime] NULL,
	[date_sortie] [datetime] NULL,
	[adresse] [varchar](30) COLLATE Latin1_General_CI_AS NULL,
	[caractere_juridique_contrat] [smallint] NULL,
	[categorie_salariale] [varchar](5) COLLATE Latin1_General_CI_AS NULL,
	[date_debut_modif_horaire] [datetime] NULL,
	[date_naissance] [datetime] NULL,
	[last_update] [datetime] NULL,
	[localite] [varchar](40) COLLATE Latin1_General_CI_AS NULL,
	[departement] [char](7) COLLATE Latin1_General_CI_AS NULL,
	[etat_civil] [smallint] NULL,
	[fonction] [smallint] NULL,
	[formation] [smallint] NULL,
	[initial_second_prenom] [char](1) COLLATE Latin1_General_CI_AS NULL,
	[langue] [smallint] NULL,
	[lieu_naissance] [varchar](40) COLLATE Latin1_General_CI_AS NULL,
	[lieu_travail] [varchar](6) COLLATE Latin1_General_CI_AS NULL,
	[motif_sortie] [smallint] NULL,
	[nationalite] [smallint] NULL,
	[niveau1] [varchar](7) COLLATE Latin1_General_CI_AS NULL,
	[niveau2] [char](4) COLLATE Latin1_General_CI_AS NULL,
	[niveau3] [char](3) COLLATE Latin1_General_CI_AS NULL,
	[num_boite] [varchar](4) COLLATE Latin1_General_CI_AS NULL,
	[num_domicile] [varchar](5) COLLATE Latin1_General_CI_AS NULL,
	[nom_travailleur] [varchar](30) COLLATE Latin1_General_CI_AS NULL,
	[prenom_travailleur] [varchar](25) COLLATE Latin1_General_CI_AS NULL,
	[nombre_heures_semaine] [decimal](4, 2) NULL,
	[code_postal] [varchar](7) COLLATE Latin1_General_CI_AS NULL,
	[matricule] [varchar](7) COLLATE Latin1_General_CI_AS NOT NULL,
	[pays_naissance] [varchar](2) COLLATE Latin1_General_CI_AS NULL,
	[pays_residence] [varchar](2) COLLATE Latin1_General_CI_AS NULL,
	[prepension_cct] [smallint] NULL,
	[rue] [varchar](30) COLLATE Latin1_General_CI_AS NULL,
	[regime_travail] [smallint] NULL,
	[regime_travail_effectif] [smallint] NULL,
	[sexe] [bit] NULL,
	[statut_travailleur] [char](1) COLLATE Latin1_General_CI_AS NULL,
	[suspension_heures_semaine] [decimal](4, 2) NULL,
	[suspension_nature] [char](3) COLLATE Latin1_General_CI_AS NULL,
	[suspension_regime_travail] [smallint] NULL,
	[suspension_tpsPlein] [bit] NULL,
	[suspension_type] [smallint] NULL,
	[temps_partiel] [bit] NULL,
	[handicape] [bit] NULL,
 CONSTRAINT [PK_personnel] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
 
GO
SET ANSI_PADDING OFF

EDIT : Notez que, à terme, la clé primaire sera le champ registre_national. J'ai momentanément ajouté un champ id car il y a des problèmes de doublons dans le rapport que je reçois d'un tiers.

Le changement des tables pour gérer la langue a été fait, je pense, avec succès.

Mon problème maintenant réside dans le fait de créer la relation entre les 2 tables dont je viens de donner le script de création.

Griftou.

Voici pour la table HumanRessources.caractere_juridique_contrat
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 ALTER TABLE HumanRessources.caractere_juridique_contrat DROP CONSTRAINT PK_caractere_juridique_contrat_langue GO ALTER TABLE HumanRessources.caractere_juridique_contrat DROP COLUMN langue GO ALTER TABLE HumanRessources.caractere_juridique_contrat ADD CONSTRAINT PK_caractere_juridique_contrat_langue PRIMARY KEY(caractere_juridique_contrat) GO CREATE TABLE HumanRessources.caractere_juridique_contrat_traduction ( caractere_juridique_contrat smallint NOT NULL CONSTRAINT FK_caractere_juridique_contrat_traduction__caractere_juridique_contrat FOREIGN KEY(caractere_juridique_contrat) REFERENCES HumanRessources.caractere_juridique_contrat , langue smallint NOT NULL CONSTRAINT FK_caractere_juridique_contrat_traduction__langue FOREIGN KEY (langue) REFERENCES <maTableDeLangues> , traduction varchar(100) NOT NULL , CONSTRAINT PK_caractere_juridique_contrat_traduction PRIMARY KEY(caractere_juridique_contrat, langue) ) GO
Citation:

EDIT : Notez que, à terme, la clé primaire sera le champ registre_national. J'ai momentanément ajouté un champ id car il y a des problèmes de doublons dans le rapport que je reçois d'un tiers.

Le mieux aurait en fait été de mettre une contrainte de clé unique de type cluster sur la colonne registre_national, et une contrainte de clé primaire non cluster sur la colonne id.
De cette façon les jointures se feront sur un entier, et les recherches seront facilitées si vous filtrez sur registre_national.

De quelles traduction avez-vous besoin pour la table personnel ?

@++ ;)

14/06/2011, 14h55
Kropernic

Ah ok !

En fait je n'avais pas du tout compris qu'il me faudrait cette table supplémentaire :aie:.

De fait, de cette manière la liaison se fera sans souci.

Citation:

Le mieux aurait en fait été de mettre une contrainte de clé unique de type cluster sur la colonne registre_national, et une contrainte de clé primaire non cluster sur la colonne id.
De cette façon les jointures se feront sur un entier, et les recherches seront facilitées si vous filtrez sur registre_national.

Je n'ai malheureusement aucune idée de ce dont vous parlez ici (surtout l'emploi du mot cluster). Je vais donc me documenter sur le sujet.

Pour finir, j'aimerais comprendre en quoi la structure que j'ai donnée n'est pas bonne. Car "sur papier", ça avait l'air de produire le résultat que je recherchais avec moins de table.

Griftou.
14/06/2011, 14h55
elsuket

En relisant le sujet, j'ai compris.
Je suppose que pour toutes les colonnes SMALLINT de votre table :

- caractere_juridique_contrat
- etat_civil
- fonction
- formation
- langue
- motif_sortie
- nationalite
- prepension_cct
- regime_travail
- regime_travail_effectif
- suspension_regime_travail
- suspension_type

Vous avez une contrainte de clé étrangère qui référence une table dans laquelle vous avez déjà mis des libellés.
Donc par exemple pour la table fonction, il faut créer une table fonction_traduction qui aurait :

- l'id_fonction en clé étrangère référençant la table fonction
- l'id_langue en clé étrangère référençant la table langue
- une contrainte de clé primaire sur ces deux colonnes
- le libellé de la fonction dans la langue.

Et il faut faire pareil pour les autres tables.
Je sais, c'est lourd :)

@++ ;)
14/06/2011, 14h58
elsuket

Ha, on a posté en même temps :)

Citation:

Je n'ai malheureusement aucune idée de ce dont vous parlez ici (surtout l'emploi du mot cluster). Je vais donc me documenter sur le sujet.

Voici un billet que j'ai écrit sur le sujet ;)

Citation:

Pour finir, j'aimerais comprendre en quoi la structure que j'ai donnée n'est pas bonne. Car "sur papier", ça avait l'air de produire le résultat que je recherchais avec moins de table.

Attention : on n'a jamais dit que ce que vous avez fait au départ ne répond pas au besoin que vous avez.

En revanche si vous voulez ajouter des langues :
- en toute tranquillité
- sans changer votre code ni la structure de vos tables
- en conservant de bonnes performances

C'est ce par quoi il faut passer : une normalisation ;)

@++ ;)
14/06/2011, 15h03
Sergejack

Citation:

Envoyé par elsuket

Le mieux aurait en fait été de mettre une contrainte de clé unique de type cluster sur la colonne registre_national, et une contrainte de clé primaire non cluster sur la colonne id.

:calim2:

Pourquoi choisir une colonne aux valeurs non séquentielles (registre_national) pour un index cluster plutôt qu'une autre (id) dont les valeurs sont séquentielles ?!
14/06/2011, 15h04
Kropernic

Citation:

Envoyé par elsuket

En relisant le sujet, j'ai compris.
Je suppose que pour toutes les colonnes SMALLINT de votre table :

caractere_juridique_contrat
etat_civil
fonction
formation
langue
motif_sortie
nationalite
prepension_cct
regime_travail
regime_travail_effectif
suspension_regime_travail
suspension_type

C'est tout à fait ça. J'ai oublié de vous le signaler dans ma dernière réponse. Toutes mes excuses.

En relisant, il y a quelque chose qui me chipote... Si j'ai bien suivi le bazar, pour les fonctions par exemple, je vais avoir :
- l'id de la fonction dans la table personnel ;
- l'id de la fonction, l'id de la langue et la traduction adéquate dans la table fonction_traduction;
- et dans la table fonction, juste un id tout seul.

C'est probablement tout à fait normal et courant dès qu'on veut faire qqch d'un peu poussé mais personnellement, avec le peu d'expérience que j'ai, ça me semble bizarre d'avoir une table avec une seule colonne dedans.

Bien à vous,

Griftou.
14/06/2011, 15h07
Kropernic

Citation:

Envoyé par elsuket

Attention : on n'a jamais dit que ce que vous avez fait au départ ne répond pas au besoin que vous avez.

En revanche si vous voulez ajouter des langues :
- en toute tranquillité
- sans changer votre code ni la structure de vos tables
- en conservant de bonnes performances

C'est ce par quoi il faut passer : une normalisation ;)

@++ ;)

Ce que j'avais au départ fonctionnait mais n'était pas du tout flexible, je suis bien d'accord.

Ma question porte sur les tables dont j'ai fourni le script. Ce modèle permettait l'ajout de langues supplémentaires non (bien que je reste sceptique quand au comportement de la table langue) ?
14/06/2011, 15h08
elsuket

Citation:

- et dans la table fonction, juste un id tout seul.

C'est probablement tout à fait normal et courant dès qu'on veut faire qqch d'un peu poussé mais personnellement, avec le peu d'expérience que j'ai, ça me semble bizarre d'avoir une table avec une seule colonne dedans.

C'est normal, j'ai supposé qu'une fonction (ou les autres entités) ont d'autres caractéristiques que leur seul libellé.
Une autre possibilité est de stocker la description (par exemple en Anglais) dans la table fonction, et les autres traductions dans la table fonction_traduction.
Si vous faites cela, il vous faudra rajouter une contrainte de domaine CHECK qui exclue la langue Anglaise.

@++ ;)
14/06/2011, 15h13
Kropernic

Ok, si c'est normal, pas de souci, je vais faire comme ça. Pour la contrainte de domaine check, mes connaissances en math me permettent de comprendre de quoi vous parler mais mes connaissances en base de données se limite à la création d'une db, l'ajout d'une clé primaire et éventuellement d'une clé étrangère.

Bref, les bases pour gérer des systèmes très simples. Alors je vais me contenter de la table avec juste un id :aie:
14/06/2011, 15h53
elsuket
Ha ben non !

Une contrainte de domaine permet de vérifier la validité d'une valeur par rapport à une règle métier simple.
Code:

1 2 ALTER TABLE maTable ADD CONSTRAINT CHK_maTable_maColonne CHECK(maColonne <> uneValeur)
Par exemple ;)

@++ ;)
14/06/2011, 16h11
Kropernic

C'est bien ce que je pensais que c'était ^^.

Et c'est vrai que ça n'a pas l'air dur à mettre en oeuvre. Je vais cependant laisser le champ id tout seul car une traduction ne sera pas plus utilisée qu'une autre. Je préfère donc laisser toutes les langues sur un pied d'égalité.

Une dernière chose...

Pour les langues, j'ai donc la table langue

Code:

1
2
3
4
5
6
7
8
9
10
11
12
/****** Object:  Table [dbo].[langue]    Script Date: 06/14/2011 16:23:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[langue](
	[langue] [smallint] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[langue] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

et la table langue_traduction

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
/****** Object:  Table [dbo].[langue_traduction]    Script Date: 06/14/2011 16:22:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[langue_traduction](
	[id] [smallint] NOT NULL,
	[langue] [smallint] NOT NULL,
	[description] [varchar](100) COLLATE Latin1_General_CI_AS NOT NULL,
 CONSTRAINT [PK_id_langue] PRIMARY KEY CLUSTERED 
(
	[id] ASC,
	[langue] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
 
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[langue_traduction]  WITH CHECK ADD  CONSTRAINT [FK_langue_traduction_langue] FOREIGN KEY([id])
REFERENCES [dbo].[langue] ([langue])
GO
ALTER TABLE [dbo].[langue_traduction] CHECK CONSTRAINT [FK_langue_traduction_langue]
GO
ALTER TABLE [dbo].[langue_traduction]  WITH CHECK ADD  CONSTRAINT [FK_langue_traduction_langue1] FOREIGN KEY([langue])
REFERENCES [dbo].[langue] ([langue])
GO
ALTER TABLE [dbo].[langue_traduction] CHECK CONSTRAINT [FK_langue_traduction_langue1]

Est-ce normal d'avoir deux clés étrangères qui font références au même champ de la même table ? Cela peut-il poser des problèmes ?

Bien à vous,

Griftou.

14/06/2011, 16h36
elsuket

Citation:

Est-ce normal d'avoir deux clés étrangères qui font références au même champ de la même table ? Cela peut-il poser des problèmes ?

Dans votre cas cela n'a pas de sens.
D'autre part si vos entités n'ont pas d'attributs, c'est qu'elles n'ont pas lieu d'être ;)
Pour l'entité langue, il me semble que seul le libellé suffit, non ?
Dans ce cas il suffit de référencer la clé primaire de la table qui la représente.
Mais il existe probablement d'autres caractéristiques !

@++ ;)
14/06/2011, 17h23
Kropernic

Citation:

Envoyé par elsuket

D'autre part si vos entités n'ont pas d'attributs, c'est qu'elles n'ont pas lieu d'être ;)

On retombe alors dans le cas des premiers scripts que j'ai donné non ?
Ce qui en résumé donne :

- la table personnel avec sa flopée de clefs étrangères (paramètres)
- pour chaque clef étrangère de la table personnel, une table composée de :
-- code du paramètre
-- code de langue
-- traduction
-- primary key (code du paramètre, code de langue)

Et j'en reviens alors mon problème de la création des relations dans mon diagramme dans management studio. Je me cite donc :

Citation:

Rebonjour,

Je reviens vers vous car j'ai un nouveau souci suite à la modification des tables de ce que j'appelle les paramètres pour gérer les langues.

Une fois cela fait, j'ai évidemment voulu recréer également les liaisons entre les différentes tables. Et c'est là que ça coince. Forcément, puisque dans ma table caractere_juridique_contrat (pour continuer avec le même exemple) se trouve repris le même id pour chaque langue (2 fois donc dans mon cas). De ce fait, la contrainte d'unicité pour établir la relation de clé étrangère (toute mes excuses si j'utilise mal le vocabulaire) n'est pas respectée et la relation ne peut se faire.

Je conçois donc bien que je dois également tenir compte de la langue mais je ne comprends pas comment cela fonctionne en pratique...

Lorsque sur le diagramme je veux faire la liaison, Management Studio me propose bien :

1 : pour la table de clé primaire :
- caractere_juridique_contrat
- langue

2 : pour la table de clé étrangère :
- caractere_juridique_contrat

Je pourrais cliquer sur ok et passer à la suite mais je préfèrerais comprendre ce qu'il se passe exactement.

Pouvez-vous m'éclairer sur ce point ? (en fait c'est tellement flou que je ne suis même pas certain d'avoir correctement formulé ma demande...)

Griftou.

Bien à vous,

Griftou.
14/06/2011, 17h45
elsuket

Pour faire cela il faudrait que vous soyez certain que toutes les entités en relation avec l'entité personnel n'auront jamais d'autres attributs que leur nom.
Est-ce bien le cas ? Réfléchissez bien aux mots en gras ...

@++ ;)
15/06/2011, 09h13
Kropernic

Citation:

Envoyé par elsuket

Pour faire cela il faudrait que vous soyez certain que toutes les entités en relation avec l'entité personnel n'auront jamais d'autres attributs que leur nom.
Est-ce bien le cas ? Réfléchissez bien aux mots en gras ...

@++ ;)

Bonjour,

Pour votre question, oui j'en suis certain. Je vais tout de même être prudent et préciser à 95%.

Par contre alors, mon problème se situe au niveau de la liaison dans management studio. Je crée les liaisons à partir de la vue du diagramme de la db (car je trouve cela plus pratique en mode graphique). Je fais donc un drag&drop en partant du champ de clef étrangère de la table personnel vers l'id de la table adéquate. Mais là, management studio ne semble pas apprécier que la clef primaire de ladite table soit une clef composée. Et en fait, c'est tout à fait logique. Du coup, je comprends pas bien comment je dois procéder pour faire cette liaison.

Griftou.

EDIT : J'ai beau retourner le truc dans tous les sens, avec mes connaissances limitées (faut bien le reconnaître), j'en arrive toujours à la conclusion que pour faire cette liaison, il me faut cette entité avec juste l'id sinon la clef étrangère ne pourra pas se faire vu qu'il n'y correspondra pas une et une seule valeur dans la table du paramètre...

J'ai tenté de trouver de la documentation sur les règles à suivre en cas de clef composée mais je n'ai rien trouvé de probant. Pas même sur le site de sqlpro (mais il est aussi possible que je sois passer à côté au milieu de ces tonnes d'informations)

Citation:

Pour votre question, oui j'en suis certain. Je vais tout de même être prudent et préciser à 95%.

héhéhé ;)

Pour ce qui est de la clé étrangère composée, comme je n'utilise jamais l'interface graphique que pour mes requêtes, je ne peux vous donner qu'un exemple de code :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 CREATE TABLE dbo.A ( a int , b int , c varchar(16) , CONSTRAINT XPKA_a_b PRIMARY KEY (a, b) ) GO CREATE TABLE dbo.B ( i int NOT NULL CONSTRAINT PKB PRIMARY KEY , a int , b int , d varchar(16) , CONSTRAINT FKB_a_b FOREIGN KEY (a, b) REFERENCES dbo.A )
Citation:

EDIT : J'ai beau retourner le truc dans tous les sens, avec mes connaissances limitées (faut bien le reconnaître), j'en arrive toujours à la conclusion que pour faire cette liaison, il me faut cette entité avec juste l'id sinon la clef étrangère ne pourra pas se faire vu qu'il n'y correspondra pas une et une seule valeur dans la table du paramètre...

Vous ne pouvez pas faire cela sinon la table qui va référencer une fonction par exemple contiendra autant de lignes que de langues, ce qui est faux.

@++ ;)

15/06/2011, 11h28
Kropernic

1 pièce(s) jointe(s)

Si seulement on pouvait faire des petits dessins plutôt qu'un long discours... :calim2:

Dans votre exemple, votre clef étrangère est sur 2 champs également. Ce n'est pas mon cas ici :aie:.

Citation:

Vous ne pouvez pas faire cela sinon la table qui va référencer une fonction par exemple contiendra autant de lignes que de langues, ce qui est faux.

Je ne crois pas... Plutôt que de rester dans la théorie, je vais tâcher de représenter un exemple concret (voir pièce jointe).

Nous avons donc la colonne fonction de la table personnel qui pointe vers la colonne id dans la table fonction et la colonne fonction de la table fonction_traduction qui pointe vers la colonne id de la table fonction.

Cela vous semble-t-il correct ?

Vous me disiez hier qu'une entité sans attribut n'a pas de raison d'être. A priori, je n'ai aucune raison de contredire cela mais alors, comment s'effectue la liaison entre la table personnel et fonction_traduction si on se passe de la table fonction ?
15/06/2011, 16h01
Kropernic

1 pièce(s) jointe(s)

Hello, c'est encore moi.

J'espère que le fait que vous ne répondez plus est dû au fait que vous avez vous aussi du travail à faire et que je ne vous ai pas (trop) exaspéré jusqu'ici :mouarf:.

EDIT : Message déplacé dans ce topic pour ne pas créer de confusion.

Griftou.
15/06/2011, 18h22
elsuket

Absolument pas :)
Je comprend tout à fait que vous vous posiez ces questions.
C'est vrai qu'aujourd'hui j'ai eu pas mal de boulot, mais avec le décalage horaire, il était temps d'aller boire une petite bière avec mes collègues ;)

Citation:

Dans votre exemple, votre clef étrangère est sur 2 champs également. Ce n'est pas mon cas ici .

Certes. Je vous montre simplement comment faire pour ajouter une clé étrangère à une table vers une autre table dont la clé primaire est composite ;)
C'est ce que vous cherchiez sans le post n° 26.

Citation:

Vous me disiez hier qu'une entité sans attribut n'a pas de raison d'être. A priori, je n'ai aucune raison de contredire cela mais alors, comment s'effectue la liaison entre la table personnel et fonction_traduction si on se passe de la table fonction ?

Cela montre mes connaissances très limitées en modélisation ...
A priori je ferai comme je l'ai pensé un peu plus haut dans la conversation, c'est-à-dire stocker le nom "par défaut" du tuple ...
Mais quelqu'un comme SQLPro vous répondra sûrement bien mieux que moi à ce sujet ...

@++ ;)

Citation:

Cela montre mes connaissances très limitées en modélisation ...

C'est un cas particulier ou votre table identifiant FONCTION ne contient aucun "attribut" non lié à une langue.

Cette table a pour moi toute son importance et pourrait par exemple avoir des notions non lié à une langue (codeFonction, Archivé oui/non etc).

Donc pour vous répondre dans le cas de FONCTION(que vous pourrez étendre aux autres...)

Table FONCTION (PK: FNC_ID)
Table LANGUE (PK: LNG_ID)
Table FONCTION_TRADUCTION (FK:FNC_ID et LNG_ID, COLONNE TRADUCTION_VALUE)

caractere_juridique_contrat à juste à référencer la table FONCTION (FNC_ID)

Ensuite créez une FONCTION TABLE (je sais que vous êtes dedans en ce moment:ccool:) comme celà:
Code:

1 2 3 4 5 6 7 CREATE FUNCTION dbo.FN_GET_PERSONNEL (@langue smallint) RETURNS TABLE AS RETURN SELECT P.ID,FT.TRADUCTION_VALUE AS FONCTION FROM dbo.PERSONNEL P LEFT OUTER JOIN dbo.FONCTION_TRADUCTION FT ON FT.FNC_ID=P.FNC_ID AND FT.LNG_ID=(@langue
Vous pourriez également créer une VUE avec CROSS JOIN sur les langues.

16/06/2011, 09h54
iberserk

Bien sûr a vous d'etoffer la fonction avec toutes les colonnes à traduire (etat civil etc.)... je vous ai montré l'exemple pour FONCTION...
16/06/2011, 10h13
Kropernic

Un tout grand merci !

J'avoue que je n'aurais jamais pensé à créer une fonction. En fait, le genre de fonction que vous proposez, ça produit tip-top ce que je pensais faire avec les vues paramétrables (qui n'existent pas :aie:) dont j'espérais l’existence dans l'autre topic.

Je vais donc m'orienter vers cela.

Citation:

Table FONCTION (PK: FNC_ID)
Table LANGUE (PK: LNG_ID)
Table FONCTION_TRADUCTION (FK:FNC_ID et LNG_ID, COLONNE TRADUCTION_VALUE)

Il s'agit bien de la structure que j'ai mis en place finalement :D

Mais je reste cependant toujours perplexe pour le cas de la table LANGUE_TRADUCTION(FK:LNG_ID1 et LNG_ID2, COLONNE TRADUCTION_VALUE) où LNG_ID1 est la valeur de la langue en elle-même et LNG_ID2 est la langue de la traduction.

Est-ce correct d'avoir 2 clefs étrangères qui référence la même clef primaire dans une table ?

Griftou.

Citation:
Envoyé par iberserk
Code:

1 2 3 4 5 6 7 CREATE FUNCTION dbo.FN_GET_PERSONNEL (@langue smallint) RETURNS TABLE AS RETURN SELECT P.ID,FT.TRADUCTION_VALUE AS FONCTION FROM dbo.PERSONNEL P LEFT OUTER JOIN dbo.FONCTION_TRADUCTION FT ON FT.FNC_ID=P.FNC_ID AND FT.LNG_ID=(@langue
Encore ~~une question~~ quelques questions...

1) Pourquoi utiliser un LEFT OUTER JOIN ici ? En anticipation du cas où un membre du personnel aurait une fonction qui ne serait pas répertoriée dans la table fonction ?

2) Comment s'utilise la fonction par après ? Dois-je l'utiliser côté applicatif ? Puis-je l'utiliser comme une table à proprement parler dans une requête ?

Merci d'avance,

Griftou.

16/06/2011, 11h47
iberserk

Simple: si vous faite un inner et que la fonction n'a pas de traduction dans la langue voulue vous ne verrez pas le personnel.... avec un left vous verrez le personnel avec FONCTION à NULL... a vous de voir...
16/06/2011, 11h49
iberserk

Citation:

Mais je reste cependant toujours perplexe pour le cas de la table LANGUE_TRADUCTION(FK:LNG_ID1 et LNG_ID2, COLONNE TRADUCTION_VALUE) où LNG_ID1 est la valeur de la langue en elle-même et LNG_ID2 est la langue de la traduction.

Est-ce correct d'avoir 2 clefs étrangères qui référence la même clef primaire dans une table ?

Je ne comprends pas cette table,je pense que vous vous êtes mal compris...
16/06/2011, 12h03
Kropernic

Citation:

Envoyé par iberserk

Je ne comprends pas cette table,je pense que vous vous êtes mal compris...

Je vais essayer d'être plus clair.

Il y a donc une table personnel comme suit :
TABLE PERSONNEL(PK: PRS_REGISTRE_NATIONAL char(11),...,LANGUE smallint,...)
où la colonne langue est la langue maternelle de la personne.

Et donc, comme pour fonction, une table langue et une table langue_traduction.

Là où la table fonction_traduction référençait la table fonction et la table langue, la table langue_traduction référencera donc 2 fois la table langue.

N'étant pas rodé dans ce genre de pratique, cette situation me semble bizarre et je voulais savoir si cela vous paraissait normal.

Bien à vous,

Griftou.
16/06/2011, 19h51
iberserk

Compris... non je ne vois pas le problème...
16/06/2011, 19h57
Kropernic

Ok bien c'est cool alors ^^.

Je suis pas du tout DBA mais comme il n'y en a pas là où je bosse (je bosse pour une chaine de magasin qui a son propre p'tit service développement), et bien il faut bien que je m'y colle un peu.

Donc merci beaucoup !