Database Schema for Organizations

[hlokomani]

Objective

Design and implement the database schema to support organizations in the application.

Tasks

1. Create an 'organizations' table with fields:
   • id (primary key)
   • name
   • username (unique)
   • description
   • creation_date
   • logo_url
   • website
   • verified_status
   • join_policy (e.g., 'open', 'request_to_join')
   • points
2. Create an 'organization_members' table with fields:
   • id (primary key)
   • organization_id (foreign key to organizations)
   • user_id (foreign key to users)
   • role (e.g., 'admin', 'member')
   • join_date
3. Add necessary indexes for performance optimization, including on the 'username' and 'join_policy' fields
4. Ensure uniqueness constraint on the 'username' field across both users and organizations tables

Acceptance Criteria

• All tables are created with proper relationships and constraints
• The 'username' and 'join_policy' fields are added to the 'organizations' table and properly indexed
• Uniqueness of usernames is enforced across both users and organizations
• Queries perform efficiently with the new schema