I make an application on JavaFX and Scene Builder
The application is similar to the TV program "Own game"
There are 8 categories in the main window.
When you click on a category, a card appears in the center of the screen. The cards will be question and not question (text sentences). If a question card appears, then there is a question on the card, and when you click on a hot key (for example, "Space"), the card is turned over (some kind of animation) and an answer appears on the card.
Question cards are associated with each category. For example: if a user clicks on the "Music" category, then a question appears (if it is a question card): "Who is your favorite singer?", If the user presses the space, the card turns over and the answer appears: "Doris Day" , for example, "Machines", then the question arises: "Who was the founder of Ford?" then when you click on the hot key, the answer to the cards appears: "Henry Ford"
In total there will be about 100 cards with questions for each category. That is, a total of 800 cards with a question and answer, and cards with simple sentences will also be about 100. Question cards are associated with answers. No question cards are associated with anything.
The logic of the appearance of cards on the main screen: 70% of question cards, 30% of non-question cards. That is, it turns out I will need to add a special random for this.
There are also buttons.
One button opens the menu for editing question cards (new stage): the user can add a new card with a question and answer to the database, delete the selected card, edit the selected card. Also there is a label that displays the letter of the English alphabet. A letter is created randomly on each card with an answer when the program starts. Also in this edit box is the number of the selected card. If the user has deleted a card with the current question, then all numbers in the database are shifted and new numbers are assigned to the cards. Card numbers are assigned automatically when creating cards. That is, the database itself assigns a number to the Label card with the question number only needed in this editing window. It displays the current number in the database. When you click on it, a list (combobox) of all card numbers from the database appears.
The second button for editing cards with sentences calls a new window (stage) it is similar to the window for editing question cards, there is only one field with textaria
The entire database of cards will be local and located in the program folder. I think SQLite is good for this. I have never worked with databases and SQLite as well. This will be my first experience.
I also want the database file to be associated with the EXCEL file, which will be located in the same folder. Since the user does not know how to open and view the database file, I decided to use the excel file, excel is on every machine and it is easy to use. The database file and the excel file need to be linked. That is, if the user made adjustments to the database from the program, the excel file was automatically updated. And vice versa: edited excel - the database was automatically updated
What is the best approach to use when working with a SQLite database?
Do two tables: one with question cards (question number, answer, answer letter) the other with offer cards (number, text, sentences)?
Or everything to push in one table? (question card number, question, answer, number of non-question card, text of the sentence)
If there are two tables for different cards, is it possible to make a chance of falling out of two tables ?: 70% / 30%
Do you have any suggestions for the implementation of my ideas?
* Design of all fxml is ready, it remains to make the logic of adding / deleting cards in the database
For a clearer picture I attach the video:
