[résolu]Conception de base de donnée

gene69 · Le 29/09/2006, à 11:37

Bonjour,
je voudrais créer une application web capable de gerer un planning de permanences et de calculer les indemnités. J'ai bien quelques idées de shémas mais c'est plein de dépendances multivaluées que je ne sais pas implémenté et des choses encores plus barbares, alors ... à l'aide.

Pour l'instant ça ressemble à ça:

TUTEURS( [ ID ] , [ Nom, Prenom, ] Adresse, NSS, Majeure, Mineures )
PERMANENCES( [ Date, Matiere, Heure_debut, Heure_fin, salle] )
PLANNING( [ ID, Date, ] Heure_debut, Duree )
TRAVAILLE( [ ID, Date, Heure_debut ] Heure_debut, Duree, Salle, coefficient, nombre_etudiants, commentaire )
EXTRA( [ ID, Date, ] remunerations, commentaire )
DISPONIBILITE( ID, ?)
COMPTABILITE(ID, ?)

jusque là tout va bien.
Je représente entre crochet les clés des tables parce que le soulignement on sait pas comment il se découpe.

Explication sémantique: Les tuteurs sont décris dans la table Tuteurs, ils ont obligatoirement un ID, un nom et prénom, une majeure. Ils peuvent aussi avoir zéro ou plusieurs mineures. Les permanences sont effectuée un jour donné, à dans un crénaux horraire et une salle précis pour une matiere déterminée.
Les tuteurs donnent leurs disponnibilité de façon inconnue (à l'aide !). Le planning est déterminé en fonction de la permanence a effectuer et de la disponnibilité des tuteurs (une bannale intersection). Une fois la permanence effectuée le tuteur consigne son travail réellement effectué (souvent il y a une modification par rapport au planning...). Les tuteurs ont la possibilité de faire des extras dont on doit garder la trace pour les rémunerer.

Premier problème:
sachant que la rémunération est calculée à partir de la table TRAVAILLE et à partir de la table EXTRA, que les rémunérations sont indexées sur le smic (qui change tous les ans), je ne sais pas comment faire le suivi des payements pour chaque tuteurs. (payé, non payé, en cours...)
deuxieme problème:
Pour la table des disponibilités j'ai aussi besoin de votre aide: comment faire pour entrer les disponibilités? à priori il y a une disponibilité périodique sur une semaine (je suis disponible le Lundi) mais parfois il y a des exceptions (le 25/12 je ne suis pas disponnible). Je ne sais pas comment représenter ce genre d'informations.

Dernière modification par gene69 (Le 02/01/2007, à 18:27)

lunique · Le 29/09/2006, à 18:27

J'ai pas bien compris le premier probleme, si c'est le smic qui change tout les ans, faudrait creer une variable qui contiendrait la valeur du smic et qui serait changeable au bon moment ^ ^
Si c'est pour inserer la somme dû a un tuteur dans comptabilité, je ferais une requete avec une colone du style sum(durée*coeff)+sum(extra) avec un group by. Apres, tu prend chque tuteur, tu met dans Compta ce qu'on lui doit pour ce mois, avec une colonne pas payé. Une fois que le virement a eu lieu, on met payé
Sinon, il represente quoi le coeff en fait ? Si c'est l'indexation par rapport au smic pour le tuteurs, sa ne devrait pas plutot se trouver dans Tuteurs?

Pour les exceptions, j'utiliserai tout betement une table qui s'appellerait exception. On verifirai d'abord que le jour concerné n'est pas dans exceptions, et s'il n'y est pas on se base sur la table "normal"

gene69 · Le 30/09/2006, à 10:11

en fait celon le type de permanence ya un coef différent au smic, c'est une juste remarque.

En fait pour la compta je crois qu'on est d'accord pour calculer les somme dues, il faut sommer extra.remuneration et travaille.duree*travaille.coefficent*smic where id=XXX.
Le probleme c'est que je ne sais pas comment faire apparaitre les indemnités versées ou pas.

Pour le calendrier des disponibilités je pense mettre un tupple par disponibilité, et pour simplifier je fait un formulaire qui accepte des "expressions réguliere" du genre "tous les lundi" et qui ensuite génère les requete qui vont bien.

Dernière modification par gene69 (Le 30/09/2006, à 10:16)

lunique · Le 30/09/2006, à 13:09

Bon, alors pour etre sur qu'on parle la meme langue ^ ^
Salaire_total=Salaire_de_base+indemnité_pour_les_extras
De ce que tu dis pour les indemnité payé ou pas, j'en conclu que que c'est payé à part.
Mais de toutes facon, on ne pourra pas considerer un extra comme les TRAVAILLE en rajoutant un attribut extra qui vaudrait 0 ou 1 et lorsque l'extra à ete payé, on met le coeff à zero, comme sa, on garde la trace de l'extra.

Au sujet des dispo, faudrait voir avec cet methode, mais sa risque de bouffer pas mal de place et de reduire les performances non ? Bon, apres si sa tourne sous un ordi recent, c'est sur qu'on est moins emmerdé que lorsque je fais une BDD sur un PII avec 300 de ram ^ ^

Lord Northam · Le 30/09/2006, à 23:40

Bonjour,

Pourquoi ne pas créer le champ pour retenir si le travaille a été rénumérée ou pas dans la table TRAVAILLE. Je suppose qu'il y aura également l'ID du Tuteur dans cette table.
Quand vient le temps de calculer la somme due à un Tuteur, on groupe les travaux actuellement non payés, on les coches comme l'étant par UPDATE, et l'on réalise l'INSERT dans la table COMPTABILITE (le prochain salaire), possédant également un champ qui cette fois signifie : En attente de paiement et deviendra payé quand ça l'est.

On peut créer une table "Valeur du Smic" (ID + Valeur + Actif) et lier l'enregistrement à chaque travail (En ajoutant l'ID du smic dans la table TRAVAILLE). Quand la valeur du smic change, on ajoute en INSERT cette nouvelle valeur, on UPDATE l'ancienne pour la désactiver (Actif passe de 1 à 0, pour connaitre la valeur actuelle a utiliser, le seul enregistrement à Actif = 1)
=> Comme ça l'historique des travaux reste valide puisque les anciens enregistrements des travaux sont liés à leurs vieux taux.

Pour bien faire, il faudrait qu'un enregistrement COMPTABILITE soit relié à X enregistrements de TRAVAILLE par le biais d'une tierce table créant les liaisons. Cette dernière servirait à obtenir le montant de COMPTABILITE par logique relationnelle (en passant par la somme des travaux jusqu'au taux du smic, aucune redondance)

J'espère être clair ^^

gene69 · Le 01/10/2006, à 13:36

En fait c'est compliqué pour le payement, et c'est vrai que je n'ai pas expliqué. Dans le cas qui nous interesse les tuteurs sont bénévoles et recoivent des indemnités différentes en fonction du type de tache (d'ou le coefficient). Ils ont aussi des activités qui ne sont pas rémunérés à l'heure mais par forfait, d'ou l'utilité de la table "extra".

Le probleme c'est que les tuteurs ne sont pas payés régulièrement comme des salariés. Le probleme c'est que les indemnités sont versés par bourses: lorsqu'un tuteur a gagné 150 € alors il recois une bourse.

L'idée de rajouté un champ payé me plait pas mal,mais alors je ne dois pas utiliser un booleen mais un poucentage ( si jamais 6 enregistrement font 1.032 bourse).

Je me fiche d'optimiser l'aplication: c'est une base qui recevra 10 requetes par semaines et le serveur de bdd sera une machine dédiée (ok il y aura d'autres bases dessus )

Merci de vos réponses

lunique · Le 01/10/2006, à 14:08

Ou alors dans tuteur, on as un attribut dû.
Dans travaille et extra, on as un booléen qui par defaut est à 0.
Regulierement, on lance une transaction qui rajoute à tuteur.dû : travaille.durée*smic*coeff et extra.remuneration où travaille.booléen=0 et extra.bool = 0.
Ensuite, on passe tout les booléns de ce tuteur à 1.
Et quand on paye une certaine somme à un tuteur, on à juste à le retirer les 150€ de tuteur.dû. Le top etant d'utiliser un moteur transactionnel comme sa, on est sûr que tout est bien fait en meme temps

Lord Northam · Le 01/10/2006, à 14:43

gene69 a écrit :

En fait c'est compliqué pour le payement, et c'est vrai que je n'ai pas expliqué. Dans le cas qui nous interesse les tuteurs sont bénévoles et recoivent des indemnités différentes en fonction du type de tache (d'ou le coefficient).

J'en déduis qu'il te faudrait une table listant, pour chaque tâche, son coefficient. (Qui sera une liste déroulante dans le formulaire)

L'idée de créer une caisse par Tuteur peut faciliter grandement les choses, mais il faudrait créer une table "Opérations" pour garder un historique de ce qui s'est passé (quand un bool 0 passe à 1):
"Travail X crédité pour machin / Extra Y crédité à bidule / Bourse Z payée"...

On est jamais à l'abri d'une erreur, et puis ça permet d'imprimer pour chaque tuteur (en utilisant son ID) le détail précis de ses opérations.

gene69 · Le 01/10/2006, à 16:33

Est ce que ceci parait pertinant?

TUTEURS( [ ID ] , [ Nom, Prenom, ] Adresse, NSS, Majeure, Mineures, Solde, commentaires)
PERMANENCES( [ Date, Matiere, Heure_debut, ] Heure_fin, Lieu )
PLANNING( [ ID, Date, Heure_debut, ] Duree )
TRAVAIL( [ ID, Date, Heure_debut, ] Duree, Lieu, Coefficient, nombre_etudiants, commentaire, Date_paye )
EXTRA( [ ID, Date, ] remunerations, commentaire, Date_paye)
DISPONIBILITE( ID, Date, Heure_debut, Heure_fin, Lieu)
avec en plus (une VUE ?)
COMPTABILITE( [ID, Année], Nombre_Bourse )

En gros comme ça je peux lister l'activité d'un tuteur avec la table TRAVAILLE. Lorsqu'on lui paye une bourse on met une date dans les champs correspondant à l'enregistrement à jour puis on calcule le nouveau solde (qu'on ne modifie que lorsqu'on fait un payement) de la table TUTEURS. Enfin on incremente le nombre de bourse pour le tuteur dans la table COMPTABILITE.

Cette solution à un énorme inconveniant à mes yeux. J'ignore si on peut faire du PL/SQL avec MySQL sachant que je n'ai que le moteur MyISAM, sans les fonctions relationnelles. Comment faire pour que mes trois requetes soient faites atomiquement? je veux dire : comment être sur que les trois modifications seront faites soit toutes les trois soit aucune des trois?

Pour lutter contre ça je peux faire une table pour les gains et une table pour les payements.

TUTEURS( [ ID ] , [ Nom, Prenom, ] Adresse, NSS, Parcours, Majeure, Mineures, commentaires)
PERMANENCES( [ Date, Matiere, Heure_debut, ] Heure_fin, Lieu )
PLANNING( [ ID, Date, Heure_debut, ] Duree )
TRAVAIL( [ ID, Date, Heure_debut, ] Duree, Lieu, Coefficient, nombre_etudiants, commentaire )
EXTRA( [ ID, Date, ] remunerations, commentaire,)
DISPONIBILITE( ID, Date, Heure_debut, Heure_fin, Lieu)
PAYEMENT( [Numero_Versement,] ID, Date, Montant, Solde_apres_payement, Commentaire)

Faire comme ça présente un avantage c'est qu'on ne modifie pas un enregistrement déjà effectué, par contre il faut faire de sacré calcul pour s'y retrouver. L'inconvénant c'est que l'enregistrement PAIEMENT.Solde_apres_payement est une information redondante dans la base.

Voilà, je préfère la deuxieme version personnellement. Quel est votre avis?

Dernière modification par gene69 (Le 04/10/2006, à 22:32)

lunique · Le 02/10/2006, à 01:30

A titre strictement personnel j'aurai preferé travaillé avec la premiere solution (mais avec un moteur InnoDB avec des procedures stockées (Mysql 5) )

Sinon, j'ai l'impression qu'il manque l'attribut date_paye dans extra.

Apparement, tu va quand meme devoir faire plein de controles meme avec la deuxieme solution puisque MyISAM ne gere pas les transactions (pour ce que j'enai compris)

gene69 · Le 02/10/2006, à 01:55

tes deux remarques sont justifiées.je corrige.

J'ai pas innobd, c'est comme ça.

Non je préfère la 2e... parce que ça ne permet de ne pas trop modifier la base... et comme ça j'ai une seule écriture à faire pour faire le payement, et non trois.

Dernière modification par gene69 (Le 02/10/2006, à 01:57)

Lord Northam · Le 02/10/2006, à 11:37

C'est volontaire de n'indiquer aucune liaison entre les tables ?

mrf · Le 03/10/2006, à 05:03

Ouh ça fait mal aux yeux
pourrais-tu renommer TRAVAILLE en TRAVAIL stp pour être cohérent

Pour le stockage du smic, tu peux faire une table "variables" (je suis pas inspiré pour le nom) et y stocker chaque année la valeur du smic ça permettra de faire du suivi...

Lord Northam · Le 03/10/2006, à 14:05

mrf a écrit :

Ouh ça fait mal aux yeux
pourrais-tu renommer TRAVAILLE en TRAVAIL stp pour être cohérent

Oui comme PAYEMENT qui devrait être PAIEMENT, mais bon.

mrf a écrit :

Pour le stockage du smic, tu peux faire une table "variables" (je suis pas inspiré pour le nom) et y stocker chaque année la valeur du smic ça permettra de faire du suivi...

Comme ça :

Lord Northam a écrit :

On peut créer une table "Valeur du Smic" (ID + Valeur + Actif) et lier l'enregistrement à chaque travail (En ajoutant l'ID du smic dans la table TRAVAILLE). Quand la valeur du smic change, on ajoute en INSERT cette nouvelle valeur, on UPDATE l'ancienne pour la désactiver (Actif passe de 1 à 0, pour connaitre la valeur actuelle a utiliser, le seul enregistrement à Actif = 1)
=> Comme ça l'historique des travaux reste valide puisque les anciens enregistrements des travaux sont liés à leurs vieux taux.

@Lunique : Si j'avais proposé une table "Opérations" pour conserver un historique, c'est bien parcequ'il n'affiche aucune relation et clés étrangères, moi aussi je trouve plus judicieux d'utiliser des tables InnoBD. Maintenant faire du MyISAM sans une table qui garde trace des opérations c'est courrir le 100m avec une béquille...

Maintenant j'en déduis qu'il utilise la base de données comme un gros bloc-notes, et que le PHP va devoir traiter pas mal de données après les requêtes.

gene69 · Le 04/10/2006, à 22:31

@Lord Northam
Merci, je sais tres bien pour innobd. Mais pour l'instant je n'ai pas le choix que de me contenter d'une base non relationnelle. Sur le serveur qu'on me prete je n'ai pas le choix.

Maintenant j'en déduis qu'il utilise la base de données comme un gros bloc-notes, et que le PHP va devoir traiter pas mal de données après les requêtes.

Ce n'est pas l'impression que me donne mes premiers dessins pour les calculs. Pour l'instant j'ai une interface assez difficile a réaliser pour la saisie des disponibilités et la table travaille. Le reste... n'a pas l'air d'être du SQL trop raffiner.

Pour le côté bloc note... je prend ça pour un compliment. C'est plus facile d'expliquer quelquechose à quelqu'un si ça fonctionne exactement comme sa méthode actuelle. Bon c'est vrai que "Bloc-note" sonne Windows et donc faux mais je ne t'en voudrais pas, tes commentaires m'ont été tres utile.

Lord Northam · Le 04/10/2006, à 23:40

Bin il ne faut guère le prendre autrement, sans relation les données sont stockées dans les tables comme dans un sac. D'ailleurs, il y a 15 ans on utilisait des fichiers Records, dont le principe était le même.

Sinon, même en MyISAM on peut réaliser des relations et du PL/SQL (en version 5).

Si l'ID d'un Tuteur ne se retrouve pas dans la table "Travail", je vois difficilement comment après tu retiens qui a fait quoi dans cette table ?

Prend ta table "PERMANENCES" par exemple, tu as un champ nommé matière.
Donc, dans le formulaire, tu as un champ texte où la personne doit l'écrire à la main, ça peut donner : "math", "Math", "mathématique", "mathématiques" où "matéhmatique".
Si tu avais une table "Matières" reliée à "Permanences", tu t'en servirais dans le formulaire comme liste déroulante.

Quand je dis que le PHP devra rattraper les faiblesses de la base de données, ce n'est pas pour me moquer, mais à l'heure actuelle, on peut créer deux permanences à la même date, aux même heures et dans la même salle/lieu pour 2 matières différentes.

Enfin, soit prudent avec les noms de tes tables et champs, déjà les majuscules ce n'est pas recommandé, mais dans Tuteurs tu as commentaires avec S et dans Paiements sans. C'est un coup à se tromper ça.

gene69 · Le 05/10/2006, à 00:40

Non, en fait je recalcule par jointure les montants dus. Comme ça apparait un peu maladroit à mes gros doigts lourds, je stocke à un endroit un resultat intermédiaire PAIEMENT.Solde_apres_paiement. Comme chaque heure de travail est pointée alors il suffit de faire si on veut recalculer combien on doit à une personne pour une perm

SELECT constante.valeur
FROM constante
WHERE travail.date BETWEEN ( constante.Date_Debut, constante.Date_Fin) AND constante.titre = "smic" ;

J'ai déjà dit que je me moque pas mal d'optimiser la table. Pour le cas d'une constante comme le smic c'est simple car il change tous les premiers juillets.

Biensur la table de "variable" je l'ai appelé CONSTANTE pour votre plus grand plaisir à tous.
CONSTANTE(Titre, Valeur, Date_Debut, Date_Fin, Commentaire)

Si on veut sportif on supprime d'attribut Date_Fin mais je ne le suis pas. J'aime autre chose que le SQL dans la vie.
un truc comme ça, sauf que ceci est invalide :

SELECT constante.valeur
FROM constante
WHERE constante IN (
SELECT constante.valeur, MIN( travail.date - constante.Date_Debut)
FROM constante
WHERE  (travail.date - constante.Date_Debut)> 0 AND   AND constante.nom = "smic" ;

gene69 · Le 05/10/2006, à 00:57

Lord Northam
pour la base de donnée, je te répète que je ne suis pas l'admin de mon serveur et que je n'ai acces à la bdd que par phpmyadmin en version 2.4.0 ... j'ai regardé l'historique chez phpmyadmin la version 2.5.0 date de 2003... Je ne balancerai pas le nom de l'admin des serveurs.
Je me suis décidé à demander l'activation des fonctions relationnelles. Par contre j'ai sans doute mal lu mais j'ai naïvement cru que MyISAM n'est pas relationnel donc pas possible. ma bdd est MySQL 3.23.55-nt.

Ta remarque est tres pertinante sur les intitulés... je pensais utiliser des formulaires à liste déroulantes j'avais pas pensé au probleme de convention de nomage, j'avions pas pensé à ça. Merci de me soumettre ce problemes.

Pour les histoires de majuscules et de noms je te suis à 200%.

Dernière modification par gene69 (Le 05/10/2006, à 00:59)

Lord Northam · Le 05/10/2006, à 10:50

Je faisais déjà du relationnel en PHP3 sur les serveurs de Multimania en 2000. Le relationnel n'est pas qu'une fonction technique, c'est aussi et surtout une idée conceptuelle de logique. Exemple :

CREATE TABLE pays (
	id_pays INT(8) NOT NULL auto_increment
	,nom VARCHAR(32) NOT NULL
	,PRIMARY KEY (id_pays)
	);
	INSERT INTO pays VALUES ('1','Belgique');
	INSERT INTO pays VALUES ('2','France');

CREATE TABLE villes (
	id_ville INT(8) NOT NULL auto_increment
	,id_pays INT(8) NOT NULL
	,nom VARCHAR(32) NOT NULL
	,PRIMARY KEY (id_ville)
	,KEY id_pays (id_pays)
	);
	INSERT INTO villes VALUES ('1','2','Paris');
	INSERT INTO villes VALUES ('2','1','Bruxelles');
	INSERT INTO villes VALUES ('3','2','Brest');
	INSERT INTO villes VALUES ('4','1','Tournai');

SELECT	villes.nom AS nom_ville_de_belgique
FROM	villes, pays
WHERE	villes.id_pays = pays.id_pays
AND	pays.id_pays = '1';

## Ne revoit que les villes de Belgique.

Ceci fonctionne en MyISAM et les données sont relationnelles. Cela facilite beaucoup la tâche.

gene69 · Le 05/10/2006, à 13:45

Oui c'est ce que je vais faire.
Mais la subtilité si j'ai bien compris c'est que le moteur MyISAM me permet de mettre une ville pour l'angleterre alors que l'angleterre n'est pas un pays déclaré. C'est ce genre de chose que le modele relationnel veut éviter.

Ensuite, j'ai une dernière question, c'est comment faire pour écrire mes contraintes d'intégritées.

Je pense bien partir sur un schéma similaire à ceux évoqués, avec la bagatelle de 10 tables (oouuuf). Même si ce n'est pas un super schéma, mais il faut bien avancer.

Merci de vos aides précieuses et répétées.

Ubuntu-fr

Navigation

Liens de recherche

Annonce

#1 Le 29/09/2006, à 11:37

[résolu]Conception de base de donnée

#2 Le 29/09/2006, à 18:27

Re : [résolu]Conception de base de donnée

#3 Le 30/09/2006, à 10:11

Re : [résolu]Conception de base de donnée

#4 Le 30/09/2006, à 13:09

Re : [résolu]Conception de base de donnée

#5 Le 30/09/2006, à 23:40

Re : [résolu]Conception de base de donnée

#6 Le 01/10/2006, à 13:36

Re : [résolu]Conception de base de donnée

#7 Le 01/10/2006, à 14:08

Re : [résolu]Conception de base de donnée

#8 Le 01/10/2006, à 14:43

Re : [résolu]Conception de base de donnée

#9 Le 01/10/2006, à 16:33

Re : [résolu]Conception de base de donnée

#10 Le 02/10/2006, à 01:30

Re : [résolu]Conception de base de donnée

#11 Le 02/10/2006, à 01:55

Re : [résolu]Conception de base de donnée

#12 Le 02/10/2006, à 11:37

Re : [résolu]Conception de base de donnée

#13 Le 03/10/2006, à 05:03

Re : [résolu]Conception de base de donnée

#14 Le 03/10/2006, à 14:05

Re : [résolu]Conception de base de donnée

#15 Le 04/10/2006, à 22:31

Re : [résolu]Conception de base de donnée

#16 Le 04/10/2006, à 23:40

Re : [résolu]Conception de base de donnée

#17 Le 05/10/2006, à 00:40

Re : [résolu]Conception de base de donnée

#18 Le 05/10/2006, à 00:57

Re : [résolu]Conception de base de donnée

#19 Le 05/10/2006, à 10:50

Re : [résolu]Conception de base de donnée

#20 Le 05/10/2006, à 13:45

Re : [résolu]Conception de base de donnée

Pied de page des forums