| | 1 | = Relational Design = |
| | 2 | |
| | 3 | == Transformation Method == |
| | 4 | |
| | 5 | 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. |
| | 6 | |
| | 7 | == Relational Schema == |
| | 8 | |
| | 9 | Primary keys are __underlined__; foreign keys are marked ''(FK)''. |
| | 10 | |
| | 11 | * '''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)'') |
| | 12 | * '''projects'''(__id__, name, description, client_id ''(FK→users)'', status, service_type, metadata, start_date, end_date, created_at, updated_at) |
| | 13 | * '''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) |
| | 14 | * '''tasks'''(__id__, title, description, status, due_date, created_at, updated_at, client_id ''(FK→users)'', project_id ''(FK→projects)'') |
| | 15 | * '''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) |
| | 16 | * '''task_workers'''(__id__, task_id ''(FK→tasks)'', user_id ''(FK→users)'') — unique(task_id, user_id) |
| | 17 | * '''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) |
| | 18 | * '''file_comments'''(__id__, file_id ''(FK→task_files)'', user_id ''(FK→users)'', content, visible_to_client, created_at) |
| | 19 | * '''task_comments'''(__id__, task_id ''(FK→tasks)'', user_id ''(FK→users)'', |
