Relational Design

Transformation Method

The relational schema below was derived from the P1 Entity-Relationship model using the partial transformation method. Each entity set became a relation; each 1:N relationship was represented by placing a foreign key on the "many" side; the single M:N relationship (Task ↔ User) was represented by a separate associative relation (task_workers). This method was chosen because the model is dominated by 1:N relationships with few optional participations, making partial transformation the most direct and readable mapping.

Relational Schema

Primary keys are underlined; foreign keys are marked (FK).

• users(id, email, password, role, name, created_at, company, logo, color_hex, address, postal_address, phone_number, extra_emails, brand_pattern, short_info, nickname, avatar_emoji, skills, profile_status, invite_token, invite_expires, referred_by_id (FK→users), company_owner_id (FK→users))
• projects(id, name, description, client_id (FK→users), status, service_type, metadata, start_date, end_date, created_at, updated_at)
• domains(id, client_id (FK→users), domain_name, is_primary, is_active, notes, activation_date, expiration_date, lifespan_years, hosting_provider, hosting_plan, hosting_expiry, status, activation_email_sent_at, renewal_reminder_sent_at, created_at, updated_at)
• tasks(id, title, description, status, due_date, created_at, updated_at, client_id (FK→users), project_id (FK→projects))
• milestones(id, task_id (FK→tasks), title, description, image_url, image_urls, pushed_to_github, deployed, is_done, done_at, done_by, created_by_id, order, created_at)
• task_workers(id, task_id (FK→tasks), user_id (FK→users)) — unique(task_id, user_id)
• task_files(id, task_id (FK→tasks), file_name, file_url, file_type, caption, section, is_completed, completed_at, completed_by, uploaded_at, uploaded_by, version, review_status, review_comment, visible_to_client)
• file_comments(id, file_id (FK→task_files), user_id (FK→users), content, visible_to_client, created_at)
• task_comments(id, task_id (FK→tasks), user_id (FK→users),