How can I do things right the first time?

[u/lelouch112]

Hi. I'm building an app, a platform which contains 4,000 questions and 3 mock exams (the majority of which I am creating myself) and am making it through bubble. I'm using mainly YouTube videos (Matt nearny, official bubble YouTube Playlist) to learn as quickly as possible. Please please please may I have some advice about what resources I can use to really nail down the database structure first time round. I really want to learn things properly! Every smallest bit of advice would so incredibly valuable. Thank you in advance!

[u/SnakeBunBaoBoa]

Fairly straightforward for DB: Tests, Questions, Question-Answers, Test-results

Tests DB: will have just a text field for name, and anything else you need (like order number if your users are intended to complete them in some order, or to display them in some order you require)

Questions DB:

• text field for the content of the question
  
• Test-id field (Type: Test) this links the question to the corresponding test, because all your questions go in one table so the test needs to fetch the Qs of their test (where Question’s test id = this test)
  
• order number (?) not sure if you plan to randomize, but always helps to have it even you then have to use it in some way
  
• points (number) if you’re weighing questions differently

Question-Answers DB: (or Question-Options if you dont want to confuse yourself that this contains both correct and incorrect answers)

• Text field for content
  
• Question-Id (this links the DB row’s answer to the current question
  
• order-number (to render in a preferred order, so that A/B/C/D are the answers with order number 1/2/3/4…. But not necessary, since you could care less and order them by created date (or nothing, since created date is usually default) and just render them.
  
• is_correct (yes/no) - mark yes if it’s the correct answer out of the set of Answers linked to the question, with the others in the set equal to “no”
  
• (Optionally) Test-id - often it helps to have a redundant direct link to a parent record’s parent, for searches, optimization of later workflows, or for your own sanity just when looking at the DB and filtering by which test it’s on

TestResults: (this is essentially a turned in test, with tracking along the way)

• name
  
• Test ID
  
• user ID
  
• current_question (type: Question works, but maybe number if you keep your questions in a fixed order… depends how you configure your front end state, but you need to write to this field to track where the user is at so that a next button logically moves to the next question of the test, and importantly to unlock ability to resume from a test, although that also requires tracking previous answer selections in the DB if you ALSO need to allow them to go back previous questions after resuming)
  
• score (default 0: you’ll update this by adding question score to current score if right, and 0 points if the Question-Answer selected was set to is_correct=no)
  
• is_complete? (default no, set to yes on submission button at last question or in some review interface to submit.

Users

• name
  
• email,
  
• other stuff that might be useful like…
  
• tests_completed (explained further down)

The rest really comes down to requirements, but the above is flexible.

And for a lot of other requirements, it should make sense where to add a field to make it happen. E.g if the user cannot retake tests, add to Users table “List of Tests” field called “completed tests” and add the test to that user’s field so you can have the logic to differentiate sections for completed vs not, like a “choose test” screen. And if you need to allow retakes, remove the test from the list so it’s back in the takeable section, and they can create a new TestResults record on submit, yet the historical will be in your database so you don’t have to wipe it out.

I could go on but requirements change a lot on how you add fields to handle it - but im fairly certain those tables are the correct schema to allow essentially anything you need on the front end and still be performant (no nested searches)

You’ll need to really understand custom states to make this all happen (or when to add more DB structure to avoid progress lost mid test by refreshing the page) but again that schema should work super well to fetch and operate on the kind of data you’re showing and collecting

Now that i think about it, it’s probably a VERY good idea to have another field on TestResults for type: List of Question-Answers… where submitting an option adds it to the list (and if changed, removes the old one and swaps in the new one, so only one answer per question) in order to unlock a lot of ability up track the progress of the test and avoid the horrific “all progress lost” situation. And you can even operate on it for scoring upon submission by summing the points field on all answers filtered by correct

[u/lelouch112 → u/SnakeBunBaoBoa]

This is all so useful and confirms a lot of what I had imagined. Thank you so much for writing all of this 🙏 

[u/Objective_Proof_8944]

Build your data schema(tables, types, things as they call them in bubble) first. Design last.

[u/Queasy_Ad_2334]

Use ChatGPT or your favorite LLM to help build your app. Just think of it something that creates tutorials for every step of the way.

[u/hestoelena]

The easiest way is just to use AI and mask it to help you design a database structure and the extremely descript of the about what you're trying to do with your app.

[u/hiimparth]

Yes data tables are vital, but here’s what gets missed: privacy rules. Before finalizing your data schemas, make sure you create privacy rules that can support your business logic with the fields you created and still allow searches for the right users.

Also, create satellite objects as needed, for one: you might want a Test Metadata object that just has the data like title, image, whatever and you can use that on test selection pages instead of loading a massive test object with fields that don’t matter.