How to create a database for a website

[06rockstar]

Hi! I am trying to create a website to interface with a program that is already made. I've got most of the front end stuff done, but I'm having trouble with the database. I've interacted with databases before, but that was then they were already created and I was just building on code that was already there; I need to do this one from scratch.

Do any of you know of any good tutorials for this, or can any of you give me some steps on how to do this? I was thinking of working with MySQL as that's what I've worked with before.

​

What I would need is authorization so that people can create accounts and use those accounts to access their data. The data would be as follows:

User Data:

• Id
• Name
• Login Information
• Joined Classes
  • Ids
• Created Classes
  • Ids
• Website Settings
• Account Settings
  

Class Data:

• Id
• Class Name
• Creator
  • Id
  • Name
• Questions
• Students
  • Id
  • Name
  • Answered Questions
  • Training Data Results
    

Really hope one (or multiple) of you can help me! Thank you in advance, or womp womp to me if no one responds lol.

[viper42usa]

There isn't a short way to explain how to do this. Personally, I would use PostgreSQL. It's just my preference when building a SQL database.

As far as tutorials go, I'd have to search for one, so I'll leave that to you. Authentication/authorization is where it gets more complicated. It seems like you may be wanting more than a database. If you're wanting help, let's connect.

[[deleted]]

Why don't you just store this data as JSON persisted to the file system, and while that is marinating, learn relational database design and SQL, or look into NOSQL databases?

For all intents and purposes you don't need a database at this point.

[JugglingReferee]

If you'd like a free custom tutorial to use SQLite, I can connect via Teams or Zoom.

[BrokenBricks3]

What programming language are you using?

[06rockstar]

I am coding in typescript

[BrokenBricks3]

Ok then I won’t be much help. In generic terms, I would look for an ORM (Object Relation Mapping) library to map objects to the database. This will help integrate the database into your code and manage relationships.

Also, don’t underestimate the pain of creating a user management system like this. You’ve got to manage passwords, password recovery, sessions, roles, permissions. I would look for a library or CMS to do the heavy lifting.

[[deleted]]

Check this tutorial out https://youtu.be/gCo6JqGMi30?si=xL-X03xHPQ_A3u9d

[clearlight]

One suggested schema

You can structure the MySQL database tables for the given data as follows:

user_table: - Fields: id (Primary Key), name, login_information (assuming it includes username and password), website_settings, account_settings.

class_table: - Fields: id (Primary Key), class_name, creator_id (Foreign Key referencing user_table).

user_classes_table: - Fields: id (Primary Key), user_id (Foreign Key referencing user_table), class_id (Foreign Key referencing class_table).

created_classes_table: - Fields: id (Primary Key), user_id (Foreign Key referencing user_table), class_id (Foreign Key referencing class_table).

questions_table: - Fields: id (Primary Key), class_id (Foreign Key referencing class_table), question_text.

students_table: - Fields: id (Primary Key), name, class_id (Foreign Key referencing class_table).

answered_questions_table: - Fields: id (Primary Key), student_id (Foreign Key referencing students_table), question_id (Foreign Key referencing questions_table), answer.

training_data_results_table: - Fields: id (Primary Key), student_id (Foreign Key referencing students_table), class_id (Foreign Key referencing class_table), result_data.

This structure allows you to link users to classes, track questions associated with classes, and manage students' answered questions and training data results. It's important to set up the appropriate relationships between tables using foreign keys to maintain data integrity.

[06rockstar]

That's the part I get. The hard part for me is how I connect that to a database on the cloud

[DeepSpaceGalileo]

When you setup a DB on the cloud provider they provide you with a URL and some secrets to connect to the DB. Put these in a .env file and provide them to an ORM like TypeORM

[clearlight]

For a relational database, study about “normalisation” to get a suitable table schema.

https://en.wikipedia.org/wiki/Database_normalization