Advice needed for a SQLite design

Databases
1

I need advice to resolve this annoyance.

This is for a Pro-Bono software (no money involved/available) I am doing for a charity association that is on the other side of the street. Of course, volunteers that use the software are retired people (> 65)…

In a DataBase (SQLite), I have a TABLE (watch the image below) where a Field “Enfant” (Child) have been added (Client’s request) and he adds there Child Name, First Name, Birth Date…).

Unfortunately, using the SQLDate for a non programmer person is very hard.
Worst, and this is the experience that is talking here, everybody nave its own way to write the date.
To be clear, not only the used field separator vary and the Day - Moth - year appearance order too.

With this, try to get stats for child (age, sex, etc.) when the State ask for them.

This Column is stored as String (every child from the family is stored in a large column / fully displayed in the Record view window).

My question is:
How can I store that set of information ?
Both in the Data base and in the Intercase (read / write).
The needed information is…
First Name,
Family Name,
Date of Birth,
Girl or Boy, [no comment here, please]
Parent (tbd)

Yes, this need a pretty large answer, but it will benefit to the Xojo users in need in the future.

PS: Actually, I store three dates (look at the TABLE representation below)::
Date of Birth,
Date In,
Date Out.

In the View by Record window, I use three PopupMenus to display each date to avoid June 31st or February 30… and to standardize the way the days appears on screen (Day as a number | Month as Name | Year as a number (99 years ago to current Year for the birth date), start of the software use (2023) + 10 for the next two PopupMenus Controls).

image

2

Ideally, present a datetimepicker control to the user.

There have also been many many many custom ones created over the years - search the forum to find one you like.
Faced with something like this to enter a date, your users should be able to get it right.

What you STORE in the database will be the SQLDate format.
You can easily read that back
When you display it, format the date in the way the users expect to see it.

1 Like
3

Storing different informations in one large field/column is a good way to make it unusable…

Create a second table “enfants” where you store those infos about the children in seperate fields (name, firstname, date of birth, sex etc.). Create a UID in the main table and use that as reference in the enfants table.
In the UI you can use a listbox to show all children of a person and a DateTimePicker to show/enter date values.

1 Like
4

Store the date as seconds since 1970. Convert an entered date to that value and convert back to the user’sdesired format to display it.

1 Like
5

2023 - 65 = 1958

:point_up_2: :+1:

6

I also prefer a readable native database format for easy inspection, including or not the time part (some servers add 00:00:00 anyway), also, you will maintain compatibility and stable date/time functions working ON the server engine part so you can ask for things as month, day, seconds, etc easily using native functions. Sometimes you just use string manipulation functions transparently.

7

As for tables and fields, that’s a long subject… You need to sit at the desk and think about all the structures and your needs identifying the ENTITIES involved, their DATA, and their RELATIONS so you can identify what tables you need, what fields, and how they are connected to each other.

You should read about the subject, here is a good summary:

1 Like
8

As far as I can see from brief testing, nothing stops SecondsFrom1970 in a DateTime from being negative.

Edit: In fact, that this is so is documented.

The database is the data storage layer, to be kept separate from the data presentation layer.

9

There’s no reason to make the server having a hard work when you could be using the SQL native way

Consider these data and values:

DROP TABLE IF EXISTS "date_test";

CREATE TABLE "date_test" (
  "date_sql" text,
  "date_num" integer
);

-- The integer value is the data/time value integer unixepoch equivalent of the SQL date/time

INSERT INTO "date_test" VALUES ('2023-04-22', 1682121600);
INSERT INTO "date_test" VALUES ('2023-05-23 15:00:00', 1684854000);
INSERT INTO "date_test" VALUES ('1898-01-22', -2270246400);

If you want to select all rows of the current year using usual SQL and proper SQL DATETIME:

SELECT
	*
FROM
	date_test 
WHERE
	SUBSTR(date_sql,1,4) = SUBSTR(CURRENT_DATE,1,4)

If you want the same using a unreadable Integer column

SELECT
	*
FROM
	date_test 
WHERE
	SUBSTR(datetime(date_num, 'unixepoch'), 1, 4) = SUBSTR(CURRENT_DATE,1,4)

Some date/time operations are accelerated using index when using native, specially compound ones as date_sql >= ‘2023’ and date_sql<=‘2024’ and you can’t do that easily using integers and heavier computations

10

you can use sql functions to handle .SQLDate or .SQLDateTime strings

https://www.sqlite.org/lang_datefunc.html

11

Thank you for all your answers.

Apparently, my question was bad asked (if you do not get the kind of answer you’re awaiting it is because of your question !).

The dates are stored as SQLDate. I do not need to store the time. The question was not here.

The question was about dealing with the Child information.

Thomas (and Rick answer with reference to read) are probably the way to go. What surprise me (only a bit) is… I used the ListBox trick nearly this way in a “Violon d’Ingres” project.

What I do not wanted to do is multiple windows to set the data securely. In an Agenda software I created (years ago) this kind of window to let the user set the Date (birth, meeting…):

image
image560×706 20.7 KB

Screen shot done from a web site as I have to search (and reach my implementation.

To be more clear, here’s a screen shot of the Record window (display the whole TABLE contents / used to Create/Read/Update/Delete Records [CRUD] and navigate thru the Record Set).

image
image2308×1203 149 KB

The Add (Ajouter) and Update (Modifier) buttons are disabled because the Record was added (or simply read).
As you can see, there is a large TextArea - named “Enfant(s” (Child) - where the user write that information. But it cannot be used to make stats since every one write data as his own).

Using the advices given here will make the process more complex for the Record creator (oldster who will use the software).

I will implement that, unless a simpler solution comes before Wednesday and show them that (or a subset) and see how it is received:
a Dialog to set the Child,
a Date selector (used three times) for… each Date.

12

You might be surprised by your users :wink: My experience is that people who are not used to working with software find it easier to enter data when the interface is uncluttered and data entry is broken up into small parts that they can understand and see what you have to enter there.
A large data-entry field isn’t as helpful if you don’t know what to enter, in what order, and in what form. Fields for first name, last name, date of birth etc. are easier to understand and use…

13

I hope so.

Here’s the window I am working on (UI for the momment, SQLite will be added later):

image
image1692×1294 149 KB

I get a “Gaz factory software…” comment, some years ago, for a (set of) windows that replace a “paper folder” (4 pages questions split on many windows).
Then I discovered the user (73 y/o this year) was incult computer wise…

14

I too don’t like that kind of data entry in a listbox row. Even if its a pro bono software, I would use a new modal window with labels and fields to enter/edit the data.

15

I use a ListBox because it allows me to set the Atoms individually and in the correct order and I do not have to fix a number of Childs. The family can have 1 or 15 childs, all is in the ListBox.

Of course, I can add a set of TextFields, but after some childs, the Dialog Window bottom will be reached…

I do not have coded that part, but when the Birth Date is entered (and empty ?), the user will see a Date Control window to set it.
Same will apply for the last Column (Boy and Girl) to choose one from the list.

16

Sorry, looks like my reply was not easy to understand: Listboxes are good to show data (here the list of children), but not to enter/edit them.
So, if this would be something I had to do, I would use a small height listbox to show if there is any child recorded. A button to create a new entry above or beneath the list and in every row a small button/picture to open the modal edit window (not everyone is used to double- or right-click a row).

17

you could store “Enfant” List with all Data into JSON String.
show it read only in the parent window and if user click into
your childs window input/edit open.
if you close this window the parent window updates information.

for buttons i would use icon+text.

18

I actually added a button to add a brand new Row (in the ListBox) and set Cell(0,0) to Edit Mode.
A press to Tab → skip to the next Cell,
On Get Focus for Birth Cell → Display the Date Picker Window,
On Get Focus for Girl | Boy → Display a “window Selector” (TBD).
Plus: Right-Click in Birth Cell display a Contextual Menu; an entry allow to disolay the Date Picker;
Same for the Girl | Boy Column: a SubMenu allow to choose Girl or Boy.

I will test with my users before implementing the Data Base code.

I do not know - yet - if I have to deal with …
same Mother and different Fathers,
different Mothers and same Father…

BTW: my Date Picker Window works fine for a 6 years old code :grinning:

19

I cannot do that; at the bottom of the List View Window, I have some fields to display that info and decoding the JSON to display its contents…

Using an Icon for the Add Child button: I love that idea !

20

Capture d’écran 2023-04-24 à 16.21.26
Capture d’écran 2023-04-24 à 16.21.26794×390 60.5 KB