Setting up simple relational database for membership system

[mor3dr3ad]

Hey there,
I have searched through some of the database examples, but have yet to wrap my head around the logic of the relations between databases, so appreciate some support. Here is what I am trying to do:

For a non-profit organisation (German Kindergarden), I am trying to set up a membership management system. Here is what it needs to do:

• Record members: these are usually one or two parents

• Record children: the children in the kindergarden

• Provide general information about members and children via queries/reports

• Provide an overview over memberships: a membership is tied to a family, which can contain one or more members as well as one or more children. The membership shall then record things like the membership fee and if there is a bank account linked already as well as start and end date.

• Provide one form or forms for:

• - entering new members

• - entering new children

• - entering new memberships

In my view, this should contain 3 tables:

• Members

• Children

• Memberships

My idea is to use the "Membership" table to create a many-to-many relationship between children and members. The name of the membership should be equal to the family name of the child. (am I missing a relationship here? probably...)

However, I have a hard time creating the databases. See below for what I have so far:

database.png (26.79 KiB) Viewed 13729 times

Now my questions:
- Am I on the right track with the general setup?
- Regarding forms: members and children are already populated, memberships not (yet). Does it make sense to have separate forms for the three databases? Because unless we have orphans, entering children w/o members makes no sense and vice versa. So would it be better to have one form for entering into all the databases?
- How would I go about creating such a form? I do know some sql, so happy to play with queries in the form wizard.

Sorry if this is really basic stuff, but would appreciate some help to point me in the right direction.
Cheers, mor3dr3ad

[eremmel]

@mor3dr3ad, do not start this kind of projects if you are not already a professional (and a professional will not likely start from scratch). I can imagine that there are organizations in Germany that support Kindergardens (Dachvertretungsorganisationen). They likely know what is good software for the administration. Likely a Kindergarden will benefit from automatic billing/banking etc as well. There are many regulations in the EU and your software has to be compliant with those rules. I guess there are good solutions without all the trouble of on-primise hosting: cloud based solutions.
When you deliver such software you will have also legal obligations / liability.

[mor3dr3ad]

I guess this wins the race for most useless comment. At no point have I specified that this needs to go into production or somehow needs to handle billing etc. This is for me to learn about how to set and maintain an appropriate database.

[Hagar Delest]

It was not actually specified but the whole post clearly shows such intent.
What kind of example have you tried and where are you lost exactly?

This query (membership keyword) may help. Examples have been discussed in the forum, maybe a Base power user has direct links at hand.

[UnklDonald418]

Database design can be tricky. When get to a design, build some Forms to test it. You wouldn't be the first to scrap the original design and start over with newly learned lessons. Often multiple times.
The table Mitgliedshaften appears to designed to reflect a Many to Many relationship between the other two tables. That will enforce unique combinations of KindID and MidgliedID but would allow a single KindID to be combined with more than one MidgliedID. Probably not what you want.
One possible solution would be to add an additional constraint. From the menu select Tools>SQL and execute

Code: Select all

ALTER TABLE "Mitgliedshaften " ADD CONSTRAINT "KID_UNIQ" UNIQUE ("KindID");

Then with Tables selected in the main Base window select View>Refresh tables to make sure that the Base front end is synchronized with the database engine.
That will allow each KindID to only appear once in Mitgliedshaften.
Since the constraint has a name it can easily be removed if needed. At Tools>SQL execute

Code: Select all

ALTER TABLE "Mitgliedshaften " DROP CONSTRAINT "KID_UNIQ" ;

Unfortunately, the word Form has two meanings in Base. In the main Base window the category Forms lists what I refer to as Form documents. These are special Writer pages that in addition to Controls can contain one or more Forms! Each of these Forms are connected to data provided by the database engine underlying Base. Controls are used to display/edit data supplied by the Form(s) on the Form document.
The Form Wizard can only design a single Form with a single SubForm. After that you will need to learn to use the editing capabilities of the Edit/Design mode. In your case you could use the the Wizard to create a Form document for Memberships. It should display all the fields in the Mitgliedshaften table on a Table Control.
With the new Form document opened in the Edit/Design mode select View>Toolbars to make sure you have the Form Design toolbar available. The Form Navigator on the Form Design toolbar allows you to overcome some of the shortcomings of the Wizard like adding more Forms and SubForms.

The columns KindID and MitgliedID will need to be changed to ListBox Controls. That will allow allow you to select KindID from a list of names in the Kinder table. Likewise, MidgliedID can be selected from a list of names from the Mitglieder table.
ListBox controls require a query that may be difficult to completely design using the Query GUI, so your SQL skills will come into play there.
The ListBox query for the KindID column would look like

Code: Select all

SELECT  "Nachname" || ', ' || "Vorname", "KindID" FROM "Kinder" ORDER BY   "Nachname", "Vorname" ASC ;

ListBox controls will not allow you to edit or enter new data into the Kinder or Mitglieder tables. You will need separate Forms for each of those.

Backup your database frequently! An .odb file is actually a Zip archive and any glitch in the archiving process can result in a corrupt .odb file, with no recoverable data!

Also, you mentioned bank account numbers. Base does not provide strong enough security or encryption features that I would trust it to store that type of information.

[Nick N,]

@ More3D-3Add

Hello,

Kindly consider following proposal (english).

At your disposal, in case you need further assistance.

Regards

Nick

[Nick N,]

@more3d3ad

My Dear Friend,

I sincerely do not understand why you have started current discussion at that time.

Would you be so kind to show-up again and post your comment, else close the discussion?

Besten Dank im Voraus
Many thanks in advance (turned into english)

Regards

Nick

[Mountaineer]

...
That will enforce unique combinations of KindID and MidgliedID but would allow a single KindID to be combined with more than one MidgliedID. Probably not what you want.
...

Really a troublesome point: If "Membership" is for accounting (Who pays for ...?), you have to know, if more than one member may pay for one child. Considering divorced couples I won't rule out this, but one may circumvent the problem by setting one member as "first in contract" and send invoices there.

But in childs care you also have another necessary relation(s): Who is allowed to fetch children,vwho to notify on accidents etc. So I guess you should add another relation from child to persons, even, if these sre not "members".

[Nick N,]

@ Uncle Donald

I would kindly ask you to consider elementary relation scheme (as show above). As regarding to contractual liens, as Mr. Mountaineer said, we do not have to forget that they can come up by selected queries case by case.

Would be pleased to have Mr. Villeroy comment too.

Regards

Nick

[Mountaineer]

...
Backup your database frequently! An .odb file is actually a Zip archive and any glitch in the archiving process can result in a corrupt .odb file, with no recoverable data!

Also, you mentioned bank account numbers. Base does not provide strong enough security or encryption features that I would trust it to store that type of information.

Base itself is only a connection to databases, with embedded HSQLDB as default. One can also use a more professional database like Postgres as backend. But personally I'd think a encrypted partition on a harddisk should be sufficient for regular data of persons.

Account numbers don't get higher protection than for example the address, but take care, if you add a table for medical data for children...

[Villeroy]

The screenshot shows a many-to-many relation between Members and Children. While each member may have many children (possibly even none), can each child belong to many members?
Does the Registration table register children or members?
Would it make sense to have a registration table for the paying members and another registration table for the children where each child belongs to one member? Or no registration table at all because each member and each child has administrative attributes in the same record?

Edit: The same child may belong to one membership today and to another membership tomorrow. The conditions of a membership may change. Many-to-many makes sense.

Edit: Start and end date should be children's attributes, so you know how many children belong to each membership at any time.

[Nick N,]

Happy to have you back Mr. Villeroy. By trying to answer your question, I would invite you to consider the Registratiion table, which, as it was, consents two parents and one child, despite the child name could be repeated (FK).

The reason why I didn't consider Brown Family is that either papa or mama could pay the rent....

Sorry for having been childish

Regards

Nick

[Mountaineer]

...
- How would I go about creating such a form? I do know some sql, so happy to play with queries in the form wizard.
...

Do you know/have read the guide to Base, available from LibreOffice.org? Considering the language in your database, you may prefer the german version, as Robert did a lot of updates.... If not start there. Included is for example a form, wich includes parts (also named forms) connected to several tables and of course also sub-forms.

Partly this also depends simply on the size of your screen, if you can fit all necessary information on one screen.

https://de.libreoffice.org/get-help/documentation/

[Nick N,]

Hello, Mr. Villeroy,

I do have some skills, thus my SQL knowledge is elementary.

As suggested by Mr. Mountaineer, whom i wish to thank for his comment, by using basically the form wizard and wanting to obtain a family snapshot, i have selected the father in the MainForm and consequentially his children in the SubForm..

Could of course do the same with a child where both mother and father contributed to his rent. It is really a piece of cake. In that case the child would be in MainForm and his parents in the SubForm, for what is worth.

Your comments are always welcome.

Regards

Nick

[Villeroy]

There is only one way to edit a many-to-many relation on a form.
1. Main form: Members
2. Subform: the mapping table "Registration" linked through the common member ID with a table control.
3. Add a listbox to the subform's table control. If the table controlwizard created the grid, right-click the "IDChild" header and replace with listbox.
3.1. Source type: Query
3.2. Source: Name of a query with the following statement:

Code: Select all

SELECT "firstname" || ' ' || "lastname" AS "fullname" FROM "Children" ORDER BY "fullname"

4. Add the other registration details to the table grid
The subform's table grid allows to pick as many children from the column of listboxes. Each registration of a child inherits the member ID from the parent form.

[Nick N,]

Dear Mr. Villeroy,

Highly appreciated your concern. Still do not understand why the inquirer is hiding himself...

Regards

Nick

[Hagar Delest]

Highly appreciated your concern. Still do not understand why the inquirer is hiding himself...

Deterred by the early comments in the topic I guess. He hasn't come back since Sept. 4th and I doubt he will log again.

[Nick N,]

I understand. I won't argue about that.

Regards

Nick