Database Schema and Models

This section describes the database schema and models used in the Vocabulary Builder application.

ER Diagram

The following ER diagram provides a visual overview of the database schema:

Database ER Diagram

SQLAlchemy Models

The database models used in this project are defined using SQLAlchemy. Below is the documentation of these models:

Database models.

class vocabulary_builder.db.models.ExampleModel(**kwargs)

Represents a usage example of an English word in a specific semantic context.

Parameters:

  • id – Primary key.

  • semantic_id – Foreign key to the associated semantic meaning.

  • example – The example text.

class vocabulary_builder.db.models.ExampleTranslationModel(**kwargs)

Represents a translation of a usage example of an English word.

Parameters:

  • id – Primary key.

  • translation_id – Foreign key to the associated translation.

  • example – The translated example text.

class vocabulary_builder.db.models.SemanticModel(**kwargs)

Represents a semantic meaning of an English word.

Parameters:

  • id – Primary key.

  • word_id – Foreign key to the associated word.

  • translations – List of translations for this semantic meaning.

  • examples – List of usage examples for this semantic meaning.

class vocabulary_builder.db.models.TranslationModel(**kwargs)

Represents a translation of an English word’s into another language.

Parameters:

  • id – Primary key.

  • semantic_id – Foreign key to the associated semantic meaning.

  • language – The target language of the translation.

  • word – The translated word.

  • examples – List of translations for the usage examples in this semantic context.

class vocabulary_builder.db.models.UserModel(**kwargs)

Represents a user in the system.

Parameters:

  • id – Primary key.

  • username – The username of the user.

  • hashed_password – The hashed password of the user.

  • favorite_words – List of user’s favorite words.

class vocabulary_builder.db.models.WordModel(**kwargs)

Represents an English word with its characteristics.

Parameters:

  • id – Primary key.

  • word – The English word.

  • part_of_speech – The part of speech of the word.

  • transcription – The transcription of the word.

  • audio – The pronunciation audio of the word (binary data).

  • semantics – List of semantic meanings of the word.

CRUD Operations

The CRUD operations for interacting with the database are defined in the crud.py module. These operations provide an interface to create, read, update, and delete records from the database.

CRUD operations for interacting with the database.

vocabulary_builder.db.crud.create_user(db: Session, username: str, hashed_password: str) → UserModel

Create a new user in the database.

Parameters:

  • db – The database session.

  • username – The username of the new user.

  • hashed_password – The plain text password of the new user.

Returns:

The created user record.

vocabulary_builder.db.crud.get_all_saved_words_for_user(db: Session, user_id: Annotated[UUID, UuidVersion(uuid_version=4)]) → list[WordModel]

Fetch all saved words for a user from the database.

Parameters:

  • db – Database session.

  • user_id – ID of the user.

Returns:

List of words saved by the user.

vocabulary_builder.db.crud.get_random_word(db: Session)

Fetch a random word from the database.

Parameters:

db – The database session.

Returns:

The random word record from the database.

vocabulary_builder.db.crud.get_user_by_username(db: Session, username: str) → UserModel

Retrieve a user by username.

Parameters:

  • db – SQLAlchemy session object.

  • username – Username to search for.

Returns:

UserModel object or None if not found.

vocabulary_builder.db.crud.remove_word_for_user(db: Session, word_id: Annotated[UUID, UuidVersion(uuid_version=4)], user_id: Annotated[UUID, UuidVersion(uuid_version=4)]) → None

Remove a word for a user in the database.

Parameters:

  • db – Database session.

  • word_id – ID of the word to remove.

  • user_id – ID of the user removing the word.

vocabulary_builder.db.crud.save_word_for_user(db: Session, word_id: Annotated[UUID, UuidVersion(uuid_version=4)], user_id: Annotated[UUID, UuidVersion(uuid_version=4)]) → None

Save a word for a user in the database.

Parameters:

  • db – Database session.

  • word_id – ID of the word to save.

  • user_id – ID of the user saving the word.