DATABASE SCHEMA AND SEEDING

[davideshett]

Description:

Set up a database schema and corresponding API endpoints to manage users, profiles, organizations, and products. This includes configuring the database, creating necessary tables with proper relationships, seeding initial data, and implementing API endpoints to retrieve user information.

Purpose:

To establish a robust database structure that supports user management, profile information, organization affiliations, and product associations, along with API endpoints to retrieve this information.

Acceptance Criteria:

Database Configuration:

Entity Framework is successfully connected to the database while Postgres is set as the default database, but allows for easy configuration of other databases. The database setup follows migrations, models, and service patterns.

Table Structure and Relationships:

The following tables are created: users, profiles, organizations, products. Relationships are correctly established: Each user has exactly one profile (one-to-one). Each user can have multiple products (one-to-many). Users can belong to multiple organizations and vice versa (many-to-many).

Data Seeding:

The database is seeded with at least 2 users. Each user has at least 2 associated products. At least 3 organizations are created in the database. User One is associated with exactly 2 of the organizations. User Two is associated with all 3 organizations.

1. EF Core Providers for Database Flexibility

Our approach is the code first approach where we write the models via code and use migrations to apply changes to the db. The database of choice is PostgreSQL. However, you can easily switch to other databases using any the following packages:

Npgsql.EntityFrameworkCore.PostgreSQL Microsoft.EntityFrameworkCore.Sqlite Microsoft.EntityFrameworkCore.SqlServer

2. Code-First Approach

Considering the choice of ORM, EntityFramework Core. It is important to add the following nugget packages:

Microsoft.EntityFrameworkCore.Design Microsoft.EntityFrameworkCore.Tools Microsoft.EntityFrameworkCore Microsoft.AspNetCore.Identity.EntityFrameworkCore

Creating Migrations

Creating migrations is a crucial step in the code-first approach, allowing us to version our database schema and keep it in sync with our model changes. Here's how to create migrations in both Visual Studio and Visual Studio Code:

Creating Migrations in Visual Studio

Open the Package Manager Console (Tools > NuGet Package Manager > Package Manager Console).

Ensure your startup project is set as the default project in the console.

To create a new migration, use the following command:

Add-Migration InitialCreate

Replace InitialCreate with a descriptive name for your migration. To apply the migration to the database, use:

Update-Database

Creating Migrations in Visual Studio Code

Open a terminal in your project directory.

Ensure you have the EF Core CLI tools installed globally:

dotnet tool install --global dotnet-ef

To create a new migration, use the following command:

dotnet ef migrations add InitialCreate

Replace InitialCreate with a descriptive name for your migration.

To apply the migration to the database, use:

dotnet ef database update

Integrating Migrations with Our Code

Once migrations are created, our application ensures they are applied when it starts up. See below:

csharp using (var scope = app.Services.CreateScope()) { / Here, the static method is used to seed users, products, profiles and organizations. The app creates a service scope and retrieves essential services: DataContext and UserManager Then call context.Database.Migrate(). Upon successful migration and database update, the following static methods will seed data into the database: Seed.SeedProfiles(context) to populate initial profile data. Seed.SeedOrganisations(context) to populate initial organisation's data Seed.SeedProducts(context) to populate initial product's data Seed.SeedUsers(userManager) to create users. / }

For clarity, this is what the static method looks like with instructions:

public static async Task SeedUsers(UserManager<User> userManager)
{
/*
 1. We read the data from a json file
 2. Then deserialized data into a list of users
 3. We then loop through the list of users and added it to the database.
 */
}

API Endpoint Implementation:

The [GET] api/v1/users/{user_id} endpoint is implemented and functional. The endpoint returns a JSON response matching the provided sample format, including: User details (name, id, email) Profile information (first name, last name, phone, avatar URL) Associated organizations (org_id, name, description) User's products (product_id, name, description)

Endpoints: ● [GET] api/v1/users/user_one_id Sample Response:

{
  "name": "John Doe",
  "id": "some-user-id",
  "email": "johndoe@example.com",
  "profile": {
    "first_name": "John",
    "last_name": "Doe",
    "phone": "1234567890",
    "avatar_url": "http://example.com/avatar.jpg"
  },
  "organisations": [
    {
      "org_id": "some-org-id-1",
      "name": "Some Org",
      "description": "Some Org Description"
    },
    {
      "org_id": "some-other-org-id-2",
      "name": "Some Other Org",
      "description": "Some Other Org Description"
    }
  ],
  "products": [
    {
      "product_id": "some-product-id",
      "name": "Some Product",
      "description": "Some Product Description"
    },
    {
      "product_id": "some-other-product-id",
      "name": "Some Other Product",
      "description": "Some Other Product Description"
    }
  ]
}

● [GET] api/v1/users/user_two_id Sample Response:

{
  "name": "John Doe",
  "id": "some-user-id",
  "email": "johndoe@example.com",
  "profile": {
    "first_name": "John",
    "last_name": "Doe",
    "phone": "1234567890",
    "avatar_url": "http://example.com/avatar.jpg"
  },
  "organisations": [
    {
      "org_id": "some-org-id-1",
      "name": "Some Org",
      "description": "Some Org Description"
    },
    {
      "org_id": "some-other-org-id-2",
      "name": "Some Other Org",
      "description": "Some Other Org Description"
    },
    {
      "org_id": "some-other-org-id-3",
      "name": "Some Other Org",
      "description": "Some Other Org Description"
    }
  ],
  "products": [
    {
      "product_id": "some-product-id",
      "name": "Some Product",
      "description": "Some Product Description"
    },
    {
      "product_id": "some-other-product-id",
      "name": "Some Other Product",
      "description": "Some Other Product Description"
    }
  ]
}

Expected Outcome:

A fully functional database schema that accurately represents the relationships between users, profiles, organizations, and products. The system should be able to store and retrieve this information efficiently. The API endpoint should return detailed user information as specified in the sample responses, including profile data, affiliated organizations, and associated products.

[nedssoft]

LGTM. Implement