Database Schema

Entity Relationship Diagram

erDiagram
    users ||--o{ user_rooms : "assigned to"
    rooms ||--o{ user_rooms : "contains"
    room_types ||--o{ rooms : "categorizes"

    users {
        INTEGER id PK "AUTO_INCREMENT"
        TEXT external_id UK "NOT NULL"
        DATETIME created_at
        DATETIME updated_at
    }

    rooms {
        INTEGER id PK "AUTO_INCREMENT"
        TEXT name "NOT NULL"
        TEXT description "DEFAULT ''"
        INTEGER room_type_id FK "NULL"
        TEXT org_id "NULL"
        DATETIME created_at
        DATETIME updated_at
    }

    room_types {
        INTEGER id PK "AUTO_INCREMENT"
        TEXT size "NOT NULL"
        TEXT style "NOT NULL"
        DATETIME created_at
        DATETIME updated_at
    }

    user_rooms {
        INTEGER id PK "AUTO_INCREMENT"
        INTEGER user_id FK "NOT NULL"
        INTEGER room_id FK "NOT NULL"
        DATETIME created_at
    }

Relationships

From	To	Type	Delete Behavior
rooms.room_type_id	room_types.id	Many-to-One	SET NULL
user_rooms.user_id	users.id	Many-to-One	CASCADE
user_rooms.room_id	rooms.id	Many-to-One	CASCADE

Constraints

Table	Constraint	Columns	Description
users	UNIQUE	external_id	Ensures unique external identifiers
user_rooms	UNIQUE	(user_id, room_id)	Prevents duplicate user-room assignments

Indexes

users

• idx_users_external_id on external_id - Fast external ID lookups
• idx_users_created_at on created_at DESC - Sorting by creation date

rooms

• idx_rooms_name on name - Fast room name searches
• idx_rooms_room_type_id on room_type_id - Filtering and joins by type

room_types

• idx_room_types_size on size - Filtering by size
• idx_room_types_style on style - Filtering by style

user_rooms

• idx_user_rooms_user_id on user_id - Fast user room lookups
• idx_user_rooms_room_id on room_id - Fast room user lookups

Table Descriptions

users

Stores user information with external authentication identifiers.

Key Features: - External ID for integration with authentication systems - Unique constraint prevents duplicate external IDs - Indexes for fast lookups and sorting

rooms

Represents physical or virtual rooms that users can be assigned to.

Key Features: - Optional categorization via room_type_id - Optional organization grouping via org_id - Cascading deletes remove user assignments when room is deleted

room_types

Defines room categories with size and style attributes.

Key Features: - Used to categorize rooms - Referenced by rooms table - Setting room's type to NULL when type is deleted (SET NULL)

user_rooms

Junction table implementing many-to-many relationship between users and rooms.

Key Features: - Many-to-many: A user can be in multiple rooms - Many-to-many: A room can have multiple users - Unique constraint prevents duplicate assignments - Cascading deletes clean up assignments when user or room is deleted

Visual Overview (ASCII)

┌─────────────┐
│   users     │
│─────────────│
│ id (PK)     │
│ external_id │◄─────┐
│ created_at  │      │
│ updated_at  │      │
└─────────────┘      │
       ▲             │
       │             │
       │ CASCADE     │
       │             │
┌──────┴──────┐      │
│ user_rooms  │      │
│─────────────│      │
│ id (PK)     │      │
│ user_id (FK)│      │
│ room_id (FK)│─┐    │
│ created_at  │ │    │
└─────────────┘ │    │
       │        │    │
       │ CASCADE│    │
       ▼        │    │
┌─────────────┐ │    │
│   rooms     │ │    │
│─────────────│◄┘    │
│ id (PK)     │      │
│ name        │      │ Many users can be
│ description │      │ assigned to many
│ room_type_id│──┐   │ rooms through the
│ org_id      │  │   │ user_rooms junction
│ created_at  │  │   │ table
│ updated_at  │  │   │
└─────────────┘  │   │
                 │   │
       SET NULL  │   │
                 ▼   │
┌─────────────┐     │
│ room_types  │     │
│─────────────│     │
│ id (PK)     │─────┘
│ size        │
│ style       │
│ created_at  │
│ updated_at  │
└─────────────┘

Cardinality

• Users ↔ Rooms: Many-to-Many (through user_rooms)
• One user can be assigned to multiple rooms

• One room can have multiple users

• Rooms → Room Types: Many-to-One

• Many rooms can have the same room type
• One room has at most one room type (nullable)

Sample SQL Queries

Assigning a User to a Room

-- Check user and room exist
SELECT id FROM users WHERE id = ?;
SELECT id FROM rooms WHERE id = ?;

-- Create assignment (UNIQUE constraint prevents duplicates)
INSERT INTO user_rooms (user_id, room_id, created_at)
VALUES (?, ?, CURRENT_TIMESTAMP);

Getting All Rooms for a User

SELECT r.*
FROM rooms r
INNER JOIN user_rooms ur ON r.id = ur.room_id
WHERE ur.user_id = ?
ORDER BY r.name;

Getting All Users in a Room

SELECT u.*
FROM users u
INNER JOIN user_rooms ur ON u.id = ur.user_id
WHERE ur.room_id = ?
ORDER BY u.external_id;

Deleting a User (Cascades)

-- This automatically deletes all entries in user_rooms for this user
DELETE FROM users WHERE id = ?;

Deleting a Room Type (Sets NULL)

-- This sets room_type_id to NULL for all rooms with this type
DELETE FROM room_types WHERE id = ?;