Information ausblenden
Willkommen im Forum für alle Datenbanken! Registriere Dich kostenlos und diskutiere über DBs wie Mysql, MariaDB, Oracle, Sql-Server, Postgres, Access uvm

Beitragssystem für Vereinsdatenbank

Dieses Thema im Forum "Datenmodellierung, Datenbank-Design" wurde erstellt von EFChris, 25 Juli 2013.

  1. EFChris

    EFChris Benutzer

    Hallo Ihr Lieben,

    ich habe vor einiger Zeit angefangen für meinen Verein eine Datenbank zu erstellen. Da wir auf die Daten von überall zugreifen wollen, hab ich mich für eine Online Lösung mit Hilfe von MySQL und PHP entschieden.
    Die Datenbank ist nun auch schon eine ganze Weile im Einsatz und funktioniert soweit ganz gut. Nur mit der Beitragsverwaltung bin ich nicht ganz zufrieden. Vielleicht habt ihr ja eine Idee wie ich das besser gestalten kann. Hier erstmal die Ausgangssituation:

    Eine MySQL Datenbank auf Webserver. Mithilfe von PHP habe ich ein kleines "User-Interface" erstellt was die Daten übersichtlich anzeigt und wo man durch einfache Formulare die Datensätze ändern und neue hinzufügen kann. Das ganze ist nur mit einem Benutzernamen und Passwort geschützt, dass nur ich erstellen kann.
    Folgende Tabellen:

    Mitglieder - enthält alle pers. Information über jedes einzelne Mitglied; Kontaktdaten; Eintrittsdatum; Beitragshöhe (wir sind eine Tanzgruppe, Beitrag wird daher durch die Anzahl der belegten Kurse berechnet)
    Kurse - alle Kurse bei uns; Wochentag; Uhrzeit
    Login - nur für die Anmeldung der Vorstandsmitglieder zum verwalten der Datenbank
    und jetzt mein Sorgenkind
    Beitraege

    Jetzt beschreib ich euch kurz wie ich mein Beitragssystem aufgebaut hab.

    Mithilfe von PHP lasse ich mir eine Tabelle für das aktuelle Jahr erstellen. Die Spalten sind die Monate und die Zeilen die Mitglieder. In der Datenbank stehen zur Zeit nur die Beitrage die bezahlt worden sind. Durch PHP lasse ich nun jeden Monat mit der entsprechenden Tabelle aus der Datenbank vergleichen ob den für den Monat ein Eintrag vorhanden ist. Wenn ja wird er als bezahlt markiert in meiner Ansicht und wenn nein dann als offener Posten. Natürlich geht die Berechnung erst NACH dem Eintrittsdatum los.
    Nun mein Problem:

    Bei uns gibt es die Möglichkeit einer "ruhenden Mitgliedschaft", d.h sollte jemand für längere Zeit nicht zu uns kommen, so kann man die Mitgliedschaft ruhen lassen und muss dafür nicht zahlen. Das konnte ich im Moment mit meiner Variante noch nicht lösen. Außerdem gibt es natürlich auch Mitglieder die kündigen. Diese werden bis jetzt immer komplett aus der Datenbank gelöscht. Wir hätten aber gern, dass die Mitglieder einfach nur als "gekündigt" markiert werden. Einige haben zu dem Zeitpunkt der Kündigung eventuell noch offene Posten in der Beitragsliste. Diesese sollen natürlich weiterhin angezeigt werden.

    Habt ihr eine bessere Lösung für mein Problem ? Weil ich denke bei jedem Aufruf die Beiträge durch PHP neu berechnen zulassen ist eher suboptimal, oder?

    Liebe Grüße

    Chris
     
  2. akretschmer

    akretschmer Datenbank-Guru

    Schon schlecht. Was passiert nächstes Jahr? Besser: eine Tabelle mit 2 Spalten: Mitglied und Bezahlt_am.

    Das kann die Datenbank berechnen - schneller als PHP.

    Extra Tabelle, die den User und die Zeiten der 'ruhenden Mitgliedschaft' enthält. In PostgreSQL würde ich da gleich zu DATERANGE greifen, in MySQL mußt da halt 2 DATE-Felder nehmen.

    Warum tust Du es nicht? Deine Mitgleider-Tabelle einfach um ein entsprechendes Feld erweitern.


    Korrekt.
     
  3. EFChris

    EFChris Benutzer

    Diese Tabelle lass ich für jedes beliebige Jahr erstellen. Das mach ich mithilfe einer GET Variable in der URL.
    Meine Tabelle "Beitraege" sieht ja schon so aus:

    Mitglied | bezahlt_fuer | bezahlt_am | Betrag
    Mitgliedsnummer | für welchen Monat | wann wurde bezahlt | wie viel wurde bezahlt

    Ok hast du einen Tipp für mich womit ich das machen kann ?

    Das hört sich ja schon mal ganz gut an. Werd ich mir mal genauer angucken. Wie sieht es aus wenn ich noch nicht weiß wie lange die ruhende Mitgliedschaft andauert ? Kann ich das damit auch lösen ?

    Das ist weniger das Problem. Wenn das andere alles funktioniert muss ich dann Kündigungen auch in der Beitragsliste versehen, da sie danach ja nicht mehr zahlen müssen.

    Vielen Dank schon einmal für die Hilfe.

    LG Chris
     
  4. akretschmer

    akretschmer Datenbank-Guru

    Kurzdemo: Du hast eine Tabelle mit dem Datum, an dem bezahl wurde:

    Code:
    test=*# select * from bezahlt ;
       datum
    ------------
     2013-07-25
    (1 row)
    
    Du willst nun von März bis August wissen, ob bezahlt wurde:
    Code:
    test=*# select s as monat, datum from generate_series(3,8) s left join bezahlt on s.s=extract(month from datum);
     monat |   datum
    -------+------------
         3 |
         4 |
         5 |
         6 |
         7 | 2013-07-25
         8 |
    (6 rows)
    

    Ja, das Ende-Feld leer lassen.


    Das mit den Kündigungen: daß sie nicht mehr zahlen müssen zeigt das Flag in der Mitglieder-Tabelle an. Es reicht, diese Info einmalig zu speichern, nur so ist es konsistent.
     
  5. EFChris

    EFChris Benutzer

    Das habe ich noch nicht ganz so verstanden.
    Hier ist mal meine Tabelle "Beitraege" aus der Datenbank:

    Code:
       
    Mitglied  | bezahlt_fuer | bezahlt_am | Betrag
    ----------+--------------+------------+--------
            1 |   2013-01-01 | 2013-01-05 | 10
            1 |   2013-02-01 | 2013-02-04 | 10
            2 |   2013-01-01 | 2013-01-04 | 10
    ...
    nun möchte ich wie beschrieben in einer Übersicht anzeigen lassen, welche Monate bezahlt sind und welche nicht. Diese Übersicht sieht ungefähr so aus:
    Code:
     Name | Jan | Feb | Mär | Apr | ...
    ------+-----+-----+-----+-----+-----
    Peter | bez | bez |  x  |  x  | ...
     Hans | bez |  x  |  x  |  x  | ...
    Mit dem Code von dir habe ich das leider nicht so hinbekommen.
     
  6. akretschmer

    akretschmer Datenbank-Guru


    Naja, du willst Zeilen zu Spalten machen, das hab ich hier in der FAQ schon mal gezeigt. Prinzipiell so:

    Code:
    test=*# select * from bezahlt ;
     name  |   datum
    -------+------------
     peter | 2013-01-01
     peter | 2013-02-01
     hans  | 2013-02-01
    (3 rows)
    
    test=*# select name, sum(case when extract(month from datum) = 1 then 1 else 0 end) as jan, sum(case when extract(month from datum) = 2 then 1 else 0 end) as feb, sum(case when extract(month from datum) = 3 then 1 else 0 end) as maerz from bezahlt group by name order by name;
     name  | jan | feb | maerz
    -------+-----+-----+-------
     hans  |   0 |   1 |     0
     peter |   1 |   1 |     0
    (2 rows)
    
    Ist so noch nicht ganz korrekt wenn da Daten über mehrere Jahre drin sind, muß man also noch passend erweitern.
     
  7. EFChris

    EFChris Benutzer

    Ok. Das habe ich soweit hinbekommen. Habe es jetzt bis zum Dezember erweitert und das gewünschte Jahr hinzugefügt.
    Jetzt bekomme ich eine Ausgabe wo steht, ob für den jeweiligen Monat ein Wert vorhanden ist (also ob bezahlt wurde) oder nicht.

    Nun möchte ich aber in meiner Übersicht sehen, wann bezahlt wurde und wie viel bezahlt wurde. Kann ich das damit auch lösen ? Und wie integriere ich das nachher in mein PHP Script so, dass die Ausgabe in meiner Tabelle erscheint ?

    LG Chris
     
  8. akretschmer

    akretschmer Datenbank-Guru

    Alles geht ;-)
    Wenn Du sowohl das Datum als auch den Betrag sehen willst, wirst Du wohl etwas in die Trickkiste greifen müssen:

    Code:
    test=# select * from zahlungen ;
     mitglied |     am     |    fuer    | betrag
    ----------+------------+------------+--------
     peter    | 2013-01-10 | 2013-01-01 |      5
     hans     | 2013-01-15 | 2013-01-01 |     10
     uwe      | 2013-02-15 | 2013-01-01 |     10
     uwe      | 2013-02-20 | 2013-02-01 |     10
     peter    | 2013-02-15 | 2013-01-01 |      5
    (5 rows)
    
    Time: 0,187 ms
    test=*# select mitglied, array_to_string(array_agg(case when extract(month from fuer) = 1 then am::text || ' ' || to_char(fuer,'TMMonth') || ' ' || betrag::text || ' EUR' else null end),', ') as januar, array_to_string(array_Agg(case when extract(month from fuer) = 2 then am::text || ' ' || to_char(fuer,'TMMonth') || ' ' || betrag::text || ' EUR' else null end),', ') as februar from zahlungen group by mitglied;
     mitglied |                      januar                      |          februar
    ----------+--------------------------------------------------+---------------------------
     hans     | 2013-01-15 Januar 10 EUR                         |
     peter    | 2013-01-10 Januar 5 EUR, 2013-02-15 Januar 5 EUR |
     uwe      | 2013-02-15 Januar 10 EUR                         | 2013-02-20 Februar 10 EUR
    (3 rows)
    
    Du hast jetzt in den Feldern das Datum drin, wann bezahl wurde, nochmals den Monat(lokalisiert!) und auch den Betrag sowie die Währung. Fehlt noch was?

    Das in PHP darzustellen ist nicht Thema hier, sollte aber auch kein Problem sein. Nur wenn Du Experten hast mit sehr vielen Teilzahlungen wird die Tabelle etwas bbbrrreeeeeiiittteeerrr...

    Andreas
     
  9. EFChris

    EFChris Benutzer

    Ok das hab ich auch verstanden. Ich bekomm nur irgendwie einen Fehler angezeigt wenn ich mir das Ausgeben lasse. Hab eigentlich alles mit meinen wirklichen Spalten Namen und so ersetzt.

    Das ist mein Code
    Code:
    select Mitglied, array_to_string(array_agg(case when extract(month from bezahlt_fuer) = 1 then bezahlt_am::text || ' ' || to_char(bezahlt_fuer,'TMMonth') || ' ' || Betrag::text || ' EUR' else null end),', ') as Jan, array_to_string(array_Agg(case when extract(month from bezahlt_fuer) = 2 then bezahlt_am::text || ' ' || to_char(bezahlt_fuer,'TMMonth') || ' ' || Betrag::text || ' EUR' else null end),', ') as Feb from Beitraege group by Mitglied
    und das bekomm ich als Fehleranzeige:
    Dann hat sich mir noch eine andere Frage ergeben. Mit diesem Code werden mir ja nur Sachen ausgegeben, die auch in der Tabelle "Beitraege" stehen. Nun möchte ich aber eine Tabelle mit ALLEN Mitgliedern. Ist ja auch möglich, dass einer noch gar nicht bezahlt hat und dann taucht er ja gar nicht auf.
     
  10. akretschmer

    akretschmer Datenbank-Guru

    Der || - Operator ist laut ANSI-SQL-Spec für die Verkettung von Zeichenketten da. Kann so eigentlich wohl jede Datenbank. MySQL aufgrund seiner Behinderungen nicht, da gibt es eine concat() - Funktion, glaube ich. Du wirst weitere Anpassungen vornehmen müssen, ich glaube nicht, daß MySQL Funktionen wie array_to_string() oder Aggregatsfunktionen wie array_agg() hat. Funktionen sind nicht Teil der SQL-Spec, hier kocht jede DB sein eigenes Süppchen, welche bei MySQL recht dünn ist.

    Nun, im richtigen Leben hat man dazu ja eine Tabelle 'Mitglieder', die alle möglichen Stammdaten enthält. Diese joint man mit seiner Tabelle 'Zahlungen'. Ich hab da mal was vorbereitet:

    Code:
    test=# create table mitglieder (id serial primary key, name text);
    NOTICE:  CREATE TABLE will create implicit sequence "mitglieder_id_seq" for serial column "mitglieder.id"
    NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "mitglieder_pkey" for table "mitglieder"
    CREATE TABLE
    Time: 25,602 ms
    test=*# insert into mitglieder (name) select distinct mitglied from zahlungen ;
    INSERT 0 3
    Time: 0,572 ms
    test=*# alter table zahlungen add column mitglied_id int references mitglieder(id);ALTER TABLE
    Time: 0,963 ms
    test=*# update zahlungen set mitglied_id = mitglieder.id from mitglieder where zahlungen.mitglied=mitglieder.name;
    UPDATE 5
    Time: 1,086 ms
    test=*# insert into mitglieder (name) values ('ich');
    INSERT 0 1
    Time: 0,291 ms
    test=*# select m.name, array_to_string(array_agg(case when extract(month from fuer) = 1 then am::text || ' ' || to_char(fuer,'TMMonth') || ' ' || betrag::text || ' EUR' else nullend),', ') as januar, array_to_string(array_Agg(case when extract(month from fuer) = 2 then am::text || ' ' || to_char(fuer,'TMMonth') || ' ' || betrag::text || ' EUR' else null end),', ') as februar from mitglieder m left join zahlungen z on (m.id=z.mitglied_id) group by m.name;
     name  |                      januar                      |          februar
    -------+--------------------------------------------------+---------------------------
     hans  | 2013-01-15 Januar 10 EUR                         |
     ich   |                                                  |
     peter | 2013-01-10 Januar 5 EUR, 2013-02-15 Januar 5 EUR |
     uwe   | 2013-02-15 Januar 10 EUR                         | 2013-02-20 Februar 10 EUR
    (4 rows)
    
    Der Name kommt jetzt aus der Mitglieder-Stammdatentabelle, die Spalte Name könnte nun in der Tabelle Zahlungen entfernt werden.
     
  11. EFChris

    EFChris Benutzer

    Ok hab die richtigen Funktionen dafür gefunden. Funktioniert soweit
    Code:
    SELECT m.Name,m.Vorname,m.ID, CONCAT(CASE WHEN EXTRACT(
    MONTH FROM bezahlt_fuer ) =1
    THEN CONCAT (DATE_FORMAT(bezahlt_am, '%d.%m.%Y'),' ', Betrag, ' EUR')
    ELSE 0
    END ) AS Jan, CONCAT(CASE WHEN EXTRACT(
    MONTH FROM bezahlt_fuer ) =2
    THEN CONCAT (DATE_FORMAT(bezahlt_am, '%d.%m.%Y'),' ', Betrag, ' EUR')
    ELSE 0
    END ) AS Feb FROM Mitglieder m
    LEFT JOIN Beitraege z
    ON m.ID = z.Mitglied
    WHERE DATE_FORMAT(bezahlt_fuer, '%Y') = '2013'
    GROUP BY m.ID
    ORDER BY m.Name
    Die hatte ich auch schon. Und wie man in meinem Code oben schon sieht konnte ich die beiden Tabellen auch miteinander verknüpfen.

    Aber trotzdem werden mir nur 122 Datensätze ausgegeben obwohl ich eigentlich 165 Mitglieder in der Datenbank hab.
     
  12. akretschmer

    akretschmer Datenbank-Guru

    Dürfte an der WHERE-Condition liegen.

    Steck halt die WHERE-Condition in den JOIN mit rein:

    Code:
    test=*# select m.name, array_to_string(array_agg(case when extract(month from fuer) = 1 then am::text || ' ' || to_char(fuer,'TMMonth') || ' ' || betrag::text || ' EUR' else nullend),', ') as januar, array_to_string(array_Agg(case when extract(month from fuer) = 2 then am::text || ' ' || to_char(fuer,'TMMonth') || ' ' || betrag::text || ' EUR' else null end),', ') as februar from mitglieder m left join (select * from zahlungen where fuer < '2010-01-01'::date) z on (m.id=z.mitglied_id) group by m.name;
     name  | januar | februar
    -------+--------+---------
     hans  |        |
     ich   |        |
     peter |        |
     uwe   |        |
    (4 rows)
    
     
  13. EFChris

    EFChris Benutzer

    Ja natürlich lag es daran. Hab ich jetzt auch hinbekommen. Neues Problem (hört wohl nie auf :/): Ich bekommen mit meiner Abfrage nur jeweils einen Datensatz pro Mitglied und Jahr. Für einen Monat hab ich alle gewünschten Angaben und bei den anderen steht immer 0. Ist die Funktion vielleicht doch noch nicht die richtige ?
     
  14. akretschmer

    akretschmer Datenbank-Guru

    Works for me:

    Code:
    test=*# select * from zahlungen ;
     mitglied |     am     |    fuer    | betrag | mitglied_id
    ----------+------------+------------+--------+-------------
     hans     | 2013-01-15 | 2013-01-01 |     10 |           3
     peter    | 2013-01-10 | 2013-01-01 |      5 |           2
     peter    | 2013-02-15 | 2013-01-01 |      5 |           2
     uwe      | 2013-02-15 | 2013-01-01 |     10 |           1
     uwe      | 2013-02-20 | 2013-02-01 |     10 |           1
    (5 rows)
    
    Time: 0,204 ms
    test=*# select
    test-#   m.name,
    test-#   array_to_string(
    test(#     array_agg(
    test(#       case when extract(month from fuer) = 1 then am::text || ' ' || to_char(fuer,'TMMonth') || ' ' || betrag::text || ' EUR' else null end
    test(#     ),', '
    test(#   ) as januar,
    test-#   array_to_string(
    test(#     array_agg(
    test(#       case when extract(month from fuer) = 2 then am::text || ' ' || to_char(fuer,'TMMonth') || ' ' || betrag::text || ' EUR' else null end
    test(#     ),', '
    test(#   ) as februar
    test-# from
    test-#   mitglieder m
    test-#   left join (
    test(#     select * from zahlungen where fuer < '2014-01-01'::date
    test(#   ) z on (m.id=z.mitglied_id)
    test-# group by m.name;
     name  |                      januar                      |          februar
    -------+--------------------------------------------------+---------------------------
     hans  | 2013-01-15 Januar 10 EUR                         |
     ich   |                                                  |
     peter | 2013-01-10 Januar 5 EUR, 2013-02-15 Januar 5 EUR |
     uwe   | 2013-02-15 Januar 10 EUR                         | 2013-02-20 Februar 10 EUR
    (4 rows)
    
    Time: 0,795 ms
    
    Da wird wohl bei Deiner Portierung nach MySQL sich ein Fehler eingeschlichen haben ...

    Andreas
     
  15. EFChris

    EFChris Benutzer

    Ja das hatte ich mir ja auch schon gedacht. Hab jetzt auf GROUP_CONCAT zurückgegriffen. Das ist dafür da um mehrere Spalten in EINEM String auszugeben. Also genau das was ich will. Nur jetzt Hab ich ein Problem, dass in der Ausgabe irgendwelche Nullen auftauchen:
    Code:
     Name  |      Januar      |      Februar      
    -------+------------------+------------------
     Hans  | 0, 07.01. 10 EUR | 08.02 10 EUR, 0
     Karl  | 19.01. 10 EUR, 0 | 0, 10.02. 10 EUR
    
    Und die Nullen sind auch genau so "willkürlich" wie ich es versucht habt anzudeuten. Mal sind sie vorn und mal hinten. Deshalb kann ich mir er nicht erklären wo sie hingehören.
    Aber ich komme der Sache näher und dafür danke ich dir schon einmal :)
     
Die Seite wird geladen...
Ähnliche Themen - Beitragssystem Vereinsdatenbank
  1. sneak
    Antworten:
    1
    Aufrufe:
    1.525

Diese Seite empfehlen

  1. Diese Seite verwendet Cookies. Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.
    Information ausblenden